Feb 262009

I developed a custom functoid and found the weird behavior that wouldn’t allow me to link the output to another functoid.  I found the reason here:


It states:

FunctoidCategory also determines the functionality restrictions and behaviors possible with the custom functoid. For example, a custom functoid that uses the Logical FunctoidCategory cannot output a string value to the map’s destination schema, but will instead determine whether the destination record is created based on the Boolean value as mentioned at: Logical Functoids.

So using FunctoidCategory.Unknown to display the functoid in the Advanced Functoid category in the VS toolbox modified its behavior to not allow its output to link to other functoids.

Feb 182009

While creating the EDI Warehouse, symptoms for EDI files that were large (10mb transactions), ampoule I was seeing 1 of two things with the SQL Adapter:

  1. Out of memory exceptions
  2. Taking hours to load data with high sql server usage

In opening a ticket with Microsoft to discover that the SQL adapter casts the message to a string in a certain part of the code, I knew that I could not use it for consistent high volume usage.

I created a Bulk XML Load adapter that accomplishes in 1 minute that previously took 3 hours to load.

This is the interface:


Some of the features are:

If the size of the message is small enough (as defined in the send handler properties) it takes the message and directly loads the data into the database. You can override the direct Stream to Database setting by changing the File Load boolean to True or if the message size is greater than the send handler property (by default set at 10mb)

Here is a screen shot of the send adapter handler, where you define where the schema needs to reside:


A sample schema’s http://targetnamespace/folder#RootNode turns into (starting in targetnamespace position 7) targetnamespace_folder_rootnode.xsd

Sample Schema Example

Enables all of the features of Bulk XML load 4.0

If an error occurs during the load, will create the xml data it is trying to load, the error.xml and a Visual Basis Script to re run the process for further testing (as long as the Debug Flag is set to True).

In the Event Log you see this entry:


This allow to manually run the bulk load to determine the error.

Feb 172009

I was seeing a weird behavior where I thought BizTalk 2006 R2 was sending a 997 in response to a received 997.  The real reason was because I had a send port filter that looked like the following:


You’ll notice that I’m subscribing to a receive port where general X12 documents are received, including 850s and 997s.  The Trading Partner is set up to generate a 997.  The real gotcha here is that there are two types of 997s, ones generated from the EDI receive pipeline known as EDI.IsSystemGeneratedAck promoted context property and ones received from your trading partner.

If you don’t set another filter to look like the following, you’ll be sending your trading partner’s 997s back to them.


Feb 052009

So there are a few companies out there that want to send out a file with a standard naming convention.

I thought, this will be EASY, you simply assign the file name to the transactions, and the Batching orchestration will re attach the context properties after its batching process completes and it sends it out so I can use %SourceFileName% in the Send Port.

WRONG, I get a file named %SourceFileName% in the folder.

To get a custom filename you have to attach the filename into the payload of the message somewhere. In my case I was needing to put the batch run id in the filename.

How I did it was put it in the ST03 element (since it gets rewritten in the EDI Send Pipeline).

I then created an orchestration that picks up messages using the filter recommendations in the party definition. Since it is a batched message, there is no schema that can be made that actually represents the data that the batching orchestration creates, I used an XMLDocument message.

Now came the problem of extracting the value from the message. I thought it would be as easy to use


However I was getting the error that the first argument needed to be a message, and since it was not a message, but a XLANGs.BaseTypes. Any message, I converted to a XMLDocument in an expression:

TempXML=new System.XML.XMLDocument(); TempXML=InMsg.MessagePart;

However I could not use the XLANGs function xpath to extract the value because I did not have a true message, so I had to use C#.

I had to create the following variables (in an atomic scope because it was late and I was tired and did not want to see if the variables were serializable or not)

Variable Name .NET Type
TempXML System.XML.XMLDocument
StringRdr System.IO.StringReader
XPathDoc System.Xml.XPath.XPathDocument
XPathNav System.Xml.XPath.XPathNavigator
XPathExpression System.Xml.XPath.XPathExpression
XPathNodeIterator System.Xml.XPath.XPathNodeIterator


In the expression shape I have the following code:

TempXML=InMsg.MessagePart; StringRdr=New System.IO.StringReader(TempXML.InnerXml); XPathDoc=new System.Xml.XPath.XPathDocument(StringRdr); XPathNav=XPathDoc.CreateNavigator; XPathExpression=XPathNav.Compile("//ST03[1]"); XPathNodeIterator=XPathNav.Select(XPathExpression); while(EPathNodeIterator.MoveNext()) { BatchId=System.Convert.ToString(XPathNodeIterator.Current.Value); }

Which extracts the value into BatchId from the message for me, now I can set the output file name to whatever is in the ST03 element.

In my case it was :


So now I can put in the send port


Feb 042009

In creating xml data from table data, I came across the need to create empty elements even in there are null values in the columns I am querying.

I first used this logic to force the generation of the tag:

SELECT EmployeeID as "@EmpID", FirstName as "EmpName/First", MiddleName as "EmpName/Middle", LastName as "EmpName/Last" FROM HumanResources.Employee E, Person.Contact C WHERE E.EmployeeID = C.ContactID AND E.EmployeeID=1 FOR XML PATH, ELEMENTS XSINIL

However that returned the result:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" EmpID="1"> <EmpName> <First>Gustavo</First> <Middle xsi:nil="true" /> <Last>Achong</Last> </EmpName> </row>

I just wanted:

<row EmpID="1"> <EmpName> <First>Gustavo</First> <Middle></Middle> <Last>Achong</Last> </EmpName> </row>

So I changed the code to:

SELECT EmployeeID as "@EmpID", ISNULL(FirstName,'') as "EmpName/First", ISNULL(MiddleName,'') as "EmpName/Middle", ISNULL(LastName,'') as "EmpName/Last" FROM HumanResources.Employee E, Person.Contact C WHERE E.EmployeeID = C.ContactID AND E.EmployeeID=1 FOR XML PATH, ELEMENTS