BizTalk ESB Exception Notification, ESB UDDI Publishing services won't start on boot

I've recently installed BizTalk 2009 on a new virtual machine (Windows Server 2003) I created. I also installed the ESB 2.0 Toolkit and the UDDI 3.0 services as well.

After each time I would boot up the machine, I would get the almighty "At least on service has failed" message. After looking into the event viewer, I noticed two outstanding errors:
'Microsoft.Practices.ESB.AlertService.AlertException' occurred and was caught.
and
Cannot open database "ESBAdmin" requested by the login. The login failed.

However, if I manually started the BizTalk ESB Exception Notification and BizTalk ESB UDDI Publishing services after all services completely started, they would start up just fine.

Both appeared to be database errors. To a degree they are, but I think it's because the MSSQLSERVER service hasn't completely finished "starting up".

It also seemed to me that the ESB services were starting before the BizTalk Server services, which didn't make much sense to me.

My solution was to create two entries in the registry:
1)Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\BizTalk ESB Exception Notification
2) Create a new multi-string value called DependOnService (case-sensitive)
3) Add entry to this value: BTSSvc$BizTalkServerApplication

Do the same thing with HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\BizTalk ESB UDDI Publishing. I also added MSSQLSERVER and SQLSERVERAGENT values to the two, just to be safe.

Now my services are starting like they should when a restart/bootup occurs on the BizTalk server.

Happy Belated Memorial Day

A wholehearted thank you to all present and past vets out there. Our country is completely indebted to you for your selfless service.

B

Figuring it all out: Replacing an XML node with a different node using XmlNode.ReplaceChild and .ImportNode

I'm working on a project that needed to do a seemingly simple thing: based on a parent node element, do a SQL query (results as xml) and then replace the result set into the existing message.

In other words, I needed to update a message with xml from a database call result.

The original xml message had the structure, but not the data. I thought it would be inefficient to loop through all nodes.

My findings on how to do this were slightly ambiguous, and most examples I looked and put into my code usually resulted in the infamous "The node to be removed is not a child of this node" error or some other ambiguous object error message.

The XmlNode.ReplaceChild(new child, old child) assumes that both child nodes are from the same document (I think - maybe the experts out there can input on this). In my case, this wasn't true, since the "new child" was a result from a SQL query.

Here's the original message, in simplified terms:

<internal>
  <routing>
    <step>
    </step>
  </routing>
</internal>

My SQL query resulted in:

<routing>
  <step>
    <sequence>1</sequence>
    <time>12:00</time>
  </step>
  <step>
    <sequence>2</sequence>
    <time>12:30</time>
  </step>
</routing>

And I needed to get to:

<internal>
  <routing>
    <step>
      <sequence>1</sequence>
      <time>12:00</time>
    </step>
    <step>
      <sequence>2</sequence>
      <time>12:30</time>
    </step>
  </routing>
</internal>

The code to complete this isn't exactly straightforward, but the important code is short. Here's how I did it:

// load XML from file and assign xPath location for node

XmlDocument docOriginal = new XmlDocument();

docOriginal.Load("C:\\test.xml");

string strNode = "/request/internal/routing";

// now get SQL from stored procedure

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@ID", strTPID));

cmd.Parameters.Add(new SqlParameter("@Process", strTPP));

conn.Open();

// load returned XML SQL into XmlDocument

XmlDocument docSQL = new XmlDocument();

using (XmlReader reader = cmd.ExecuteXmlReader())
{
docSQL.Load(reader);
}

The next three lines of code are what actually do the magic:

XmlNode oldNode = docOriginal.SelectSingleNode(strNode);
XmlNode newNode = docOriginal.ImportNode(docSQL.DocumentElement, true);
oldNode.ParentNode.ReplaceChild(newNode, oldNode);
return docOriginal;

There's a little bit going on here to fully understand and appreciate this:

1. The first line explains itself – get the node that I want out of the xml document I loaded from file.
   
2. This piece initially was a little hard for me to comprehend, which was the crux of my frustration. The .ReplaceChild in the next line of code seemingly must have the child nodes from the same xmlDocument. Therefore I could not just take any xml and slap it in. The .ImportNode command assigns the SQL result to a new node via docOriginal. So effectively we are saying: take the SQL result and create a new node based on the docOriginal XmlDocument.
3. Step 3 is now self-explanitory and now you don't get those darn error messages.

BizTalk Get Common Value, Get Application Value Functoids

You don't hear much about the "Get" or "Set" database functoids in the BizTalk mapper. Eric and Tom have blogged on these (and most likely a bit more in-depth).

These functoids are actually quite useful. For example the "Get Common Value" and "Get Application Value" functoids which I'm currently using.



Scenario - I have a trading partner that sends in an EDI document (850 purchase order request). The unit of measurement list in the X12 standards are two characters long. They don't exactly match up with the system I'm trying to send to (PeopleSoft) - some can be three characters. So I'm thinking "a database lookup table will work perfect for this."

I could do one of two things: a custom database table or use the BizTalk-supplied functionality using the xref_ tables supplied in the BizTalkMgmtDb. In fact, this is exactly what the BizTalk developers had in mind for this, I think. :)

At any rate, for simple lookups, 3 tables are used: xref_AppType, xref_ValueXRef, and xref_ValueXRefData. The relationship is fairly straightforward.


The above screenshot essentially describes the "Get Common Value" in SQL terms. The parameters are in the same order as the functoid expects (the 'Value' most likely the input to the functoid, which would be the 3rd parameter). With the "Get Application Value" functoid, the commonValue field and appValue are effectively switched/reversed, essentially making it a "select appValue where commonValue = xxx".

There is a BizTalk utility (BTSXRefImport.exe) to load the data in, but in this case I decided to load the data in directly via SQL Server (guilty as charged - I like working directly with databases).

Hope this helps clarify what these two functoids can do.

Installing Oracle's ODBC drivers

One of my projects requires an ODBC connection from a Windows environment to Oracle 10x. As most of you know, it's difficult to find step-by-step instructions on how to install it. So this is what I've done so far to at least get the driver installed:

1. Download Oracle InstantClient (Basic) and the InstantClient ODBC suppliment (http://www.oracle.com/technology/tech/oci/instantclient/index.html). It’s a much smaller footprint than installing the whole Oracle Client.
2. Unzip both files to folder. Copy root folders to a folder like C:\Oracle\instantclient (basic and supplement will be in same folder).
3. Run odbc_install.exe from command prompt – you can see results better than double-clicking the executable.
4. Add Oracle’s System Variables (Windows control panel\System\Advanced tab, click Environment Variables, click Add for System Variables):
   LD_LIBRARY_PATH
   PATH (append)
   SQLPATH
   TNS_ADMIN
5. Add (create) TNSNAMES.ORA file to folder described above in TNS_ADMIN.
6. Create ODBC data source connection the typical way in Windows.
7. And there you have it! To uninstall, first delete ODBC connection that you created in step 6, then remove envronment variables from step 4, then run odbc_uninstall.exe.

Using BizTalk Value Mapping (flattening) and Looping functoids together

I have a map that I've been working on and needed to flatten out a source (looped) to target (flattened out). Basically a looped XML source to an X12 EDI destination. Currently I'm developing in BizTalk 2006 (not R2).

My source XML in question looks like this:

<BI_XHD_CNT_TAO class="R">

<SETID>ID</SETID>

<CUST_ID>123456</CUST_ID>

<BUSINESS_UNIT>SomeBusiness</BUSINESS_UNIT>

<INVOICE>12345</INVOICE>

<NOTES_SEQ_NUM>2</NOTES_SEQ_NUM>

<NOTE_TYPE>SHIPPING</NOTE_TYPE>

<TEXT254>Tracking: 987654321</TEXT254>

</BI_XHD_CNT_TAO>

<BI_XHD_CNT_TAO class="R">

<SETID>ID</SETID>

<CUST_ID>123456</CUST_ID>

<BUSINESS_UNIT>SomeBusiness</BUSINESS_UNIT>

<INVOICE>12345</INVOICE>

<NOTES_SEQ_NUM>2</NOTES_SEQ_NUM>

<NOTE_TYPE>SHIPPING</NOTE_TYPE>

<TEXT254>Carrier: UPS GRND</TEXT254>

</BI_XHD_CNT_TAO>

.

. (more iterations - other stuff in the TEXT254, but just the above is what I want)

.

I get multiple iterations of this information - the TEXT254 info is what I'm looking for. I need to extract and flatten the information to different parts of my destination - an X12 EDI 810 (invoice). The SAC segment has two elements - SAC_04 (shipping carrier name (i.e. FedEx) for the trading partner), and the SAC_13 is the tracking number.

Source and Destination:

I mapped the source and destination using the Value Mapping (flattening) functoid, and had to do some minor string manipulation (i.e if the text starts with “Carrier” or “Tracking” then map it out) but I wasn't getting the results I was looking for. I was still getting multiple iterations of the SAC segment – and I only can have one. Initially my output XML looked like this:

<SACLoop2>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC04="FED EX"/>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC13="123456"/>

</SACLoop2>

and what I wanted to get was this:

<SACLoop2>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC04="FED EX" SAC13="123456" />

</SACLoop2>

After understanding what was going on, I realized I needed to add a Looping functoid, as shown below (and selected):

This flattened out everything out further and gave me the results I was looking for:

<SACLoop2>

<SAC_3 SAC01="C" SAC02="D200" SAC03="ZZ" SAC04="FED EX" SAC13="123456" />

</SACLoop2>

Hope this helps out. Other suggestions are always welcome.

Welcome!

Welcome to my blog!

This blog will be semi-technical in nature mostly focused within the Microsoft world.

I work as a vb.net/tfs developer in the Midwest. I used to consult, but grew tired of poorly matched engagements.

I've done BizTalk in the past, and have touched InfoPath, SharePoint, heavy EDI and other stuff.

Hope I can make contributions to the developers out there that have gone bald by constantly pulling their hair out...