Jan 302009

I had a requirement to extract several thousand rows of X12 data from a back end database and package them up once a month into X12 EDI with a max of 5000 rows per Transaction Set and maximum 4 Transaction Sets per file.  I found that there was no out of the box way to loop around each of the 5000 rows and have them batched.  Additionally, if I had 3 of the 4 final transaction sets in the last batch, it wouldn’t release.  Since the process only runs 1 time per month, I needed the batch to package up the final 3 transactions sets and drop them off as soon as the orchestration finishes.

Here’s what I did:

ST01 and ST02 needed to be populated, in my case I set ST01 to 837 and ST02 to 0001.


You’ll  notice that the Microsoft.BizTalk.Edi.BatchingOrchestration.BatchingService subscribes to the following promoted properties.


Since my send shape was within a loop, I couldn’t simple create a correlation set and promote the properties that way.  Instead I created a separate orchestration whose only function was to promote the EDI properties the Microsoft.BizTalk.Edi.BatchingOrchestration.BatchingService orchestration was looking for.  The orchestration contained an input message parameter of the X12 EDI document I was sending out, with a correlation set initialized on the send shape.  Here is the orchestration I named orch_PromoteEDIBatchingProperties.odx


My ct_EDIPropertiesToBePromoted properties:


My main orchestration simply called this orchestration and the messages were successfully being picked up by the Microsoft.BizTalk.Edi.BatchingOrchestration.BatchingService orchestration.  Now I needed some way to release the final batch upon orchestration completion.

If you look at the properties of the BatchControlMessageRecvLoc Receive Location, you’ll notice that it executes a simple stored procedure that returns a message to release the batch.  It executes the Stored Procedure edi_GetControlMessage that looks at the PAM_Control Table found in the BizTalkMgmtDb.


All I needed to do was to populate the PAM_Control table with a row that the stored procedure would pick up saying it needed to terminate the batch.  I simply inserted the DestinationPartyID, EdiMessageType, ActionType, ActionDateTime, and UsedOnce values needed into the table and it releases the batch as soon as the sproc is executed, in this case every 30 seconds.  I created a schema for a table insert and in my case inserted 15, 0, EdiBatchOverride, System.DateTime.Now, and 0 for the PAM_Control Table values.  As soon as the Stored Procedure ran it flags the row as having been UsedOnce.


The important parts of my orchestration are shown below


The MessageAssignment shape has the following code.

//Instantiate your XmlDocument
xml_Temp = new System.Xml.XmlDocument();
//Load the XmlDocument with the information needed for the PAM_Control Table
xml_Temp.LoadXml(“<ns0:Request xmlns:ns0=\”PAM_Control\”><ns0:sync><ns0:after><ns0:PAM_Control DestinationParty=\”15\” EdiMessageType=\”0\” ActionType=\”EdiBatchOverride\” ActionDateTime=\””+System.DateTime.Now.ToString()+”\” UsedOnce=\”0\” /></ns0:after></ns0:sync></ns0:Request>”);
//Assign your XmlDocument to your msg_BatchControlRequest
msg_BatchControlRequest = xml_Temp;

I am planning on creating two parameterized orchestrations that will be able to be used as called orchestrations for my other projects instead of this approach that is coded within my main orchestration.

Jan 292009

I’ve come across the same situation twice, so time to blog about it.  I have an EDI segment that can take 4 line procedure codes.  The back end system contains the 4 procedure codes, but it is not guaranteed which order they will be populated or if they will be populated at all.  For example procedure code 1 could be NULL but 2, 3, and 4 could be present.  The EDI segment requires that procedure codes be populated starting at element 1.

I created an inline C# that would accommodate this scenario.  Throw all your procedure modifiers into the scripting functoid and adjust the int_ProcedureModifierToReturn to the one you’re needing and away you go.

public string get_2400_SV101_2(string LINE_PROC_MODIFIER1, string LINE_PROC_MODIFIER2, string LINE_PROC_MODIFIER3, string LINE_PROC_MODIFIER4) { int int_ProcedureModifierToReturn = 0; //All Data will be in the form of example: BH*A2*20041201^BI*A2*20041201-20041215 string[] arry_ProcedureModifiers; string str_ProdedureModifiers = LINE_PROC_MODIFIER1 + "^" + LINE_PROC_MODIFIER2 + "^" + LINE_PROC_MODIFIER3 + "^" + LINE_PROC_MODIFIER4; //Split the LINE_PROC_MODFIERS into an array arry_ProcedureModifiers = str_ProdedureModifiers.Split('^'); //Sort the Array to get like Qualifiers together System.Array.Sort(arry_ProcedureModifiers); //Reverse the array order to get the blank records to the end System.Array.Reverse(arry_ProcedureModifiers); //Return the element if it exists if (arry_ProcedureModifiers[int_ProcedureModifierToReturn].Length > 0) { return arry_ProcedureModifiers[int_ProcedureModifierToReturn]; } else { return ""; } }

Jan 262009

A friend from Chile asked about how to extract a filename from a message, after going back and forth, I sent him this code that I use in my EDI logger

private static string extractFileName(IBaseMessage inmsg) { string adapterType = (string)inmsg.Context.Read("InboundTransportType", "http://schemas.microsoft.com/BizTalk/2003/system-properties"); string ReceivedFileName = ""; //make sure it is an adapter that we can get a file name from if (adapterType == "FILE" || adapterType == "FTP") { if (adapterType == "FILE") { ReceivedFileName = (string)inmsg.Context.Read("ReceivedFileName", "http://schemas.microsoft.com/BizTalk/2003/file-properties"); } else if (adapterType == "FTP") { ReceivedFileName = (string)inmsg.Context.Read("ReceivedFileName", "http://schemas.microsoft.com/BizTalk/2003/ftp-properties"); } } return Path.GetFileName(ReceivedFileName); }

Since he said he could not find anything out there on how to do it, I figured I would post it for everyone’s edification.

Jan 092009

I was currently developing the Bulk XML import adapter and I had developed the adapter and tested it on my development box. I then proceeded to create the MSI.

So you go and create the code for the adapter, and test the functionality of the logic.

After you are done, you have to create the Adapter Registry File. The easiest way to do this is to use the Adapter Registry Wizard, which will create a registry file.

Here is the sample file I created:

Windows Registry Editor Version 5.00 [HKEY_CLASSES_ROOT\CLSID\{16e07168-8136-4804-8a6f-33053ad09d62}] @="SQLBulkXML Adapter" "AppID"="{14E0EF65-3B1B-4e29-9923-C13CDE2998B0}" [HKEY_CLASSES_ROOT\CLSID\{16e07168-8136-4804-8a6f-33053ad09d62}\BizTalk] @="BizTalk" "TransPortType"="SQLBulkXML" "Constraints"=dword:00002502 "OutboundProtocol_PageProv"="{2DE93EE6-CB01-4007-93E9-C3D71689A283}" "TransmitLocation_PageProv"="{2DE93EE6-CB01-4007-93E9-C3D71689A282}" "OutboundEngineCLSID"="{d70b9447-641d-4af7-8324-9996d124a66a}" "OutboundTypeName"="StottIS.BizTalk.Adapters.Runtime.SQLBulkXMLTransmitter.SQLBulkXMLTransmitAdapter" "OutboundAssemblyPath"="C:\\WINDOWS\\assembly\\GAC_MSIL\\SQLBulkXMLTransmitAdapter\\\\SQLBulkXMLTransmitAdapter.dll" "AdapterMgmtTypeName"="StottIS.BizTalk.Adapters.SQLBulkXMLDesignTime.AdapterManagement" "AdapterMgmtAssemblyPath"="C:\\WINDOWS\\assembly\\GAC_MSIL\\SQLBulkXMLAdapterManagement\\\\SQLBulkXMLAdapterManagement.dll" "AliasesXML"="<AdapterAliasList><AdapterAlias>sqlbulkxml://</AdapterAlias></AdapterAliasList>" "PropertyNameSpace"="http://schemas.microsoft.com/SQLBulkXML" "SendHandlerPropertiesXML"="<CustomProps><AdapterConfig vt=\"8\"/></CustomProps>" "SendLocationPropertiesXML"="<CustomProps><AdapterConfig vt=\"8\"/></CustomProps>" [HKEY_CLASSES_ROOT\CLSID\{16e07168-8136-4804-8a6f-33053ad09d62}\Implemented Categories] [HKEY_CLASSES_ROOT\CLSID\{16e07168-8136-4804-8a6f-33053ad09d62}\Implemented Categories\{7F46FC3E-3C2C-405B-A47F-8D17942BA8F9}]

Which created the following entry in the registry:


I created the adapter, registered the adapter in the registry and then imported the adapter into the adapter list and configured it.

Things worked great, and I was ready to create the install package so I could use the code in other environments.

I installed the code and started running, however, I started getting errors that the connection string was not property formatted.

This did not make sense, so I put the code on the installed box and started debugging the code.

When I got to the loading of the configuration, I was getting a null string for the AdapterConfig in this method:

public SQLBulkXMLTransmitProperties(IBaseMessage message, string propertyNamespace) { XmlDocument locationConfigDom = null; string config = (string) message.Context.Read("AdapterConfig", propertyNamespace); this.isTwoWay = (bool) message.Context.Read(isSolicitResponseProp.Name.Name, isSolicitResponseProp.Name.Namespace); if (null != config) { locationConfigDom = new XmlDocument(); locationConfigDom.LoadXml(config); this.LocationConfiguration(locationConfigDom); } }


So when it got to the resolution of the AdapterConfig:

public string ConnectionString { get { return this.connectionString; } }

It would resolve to null.


The issue ended being in the install project, I put http://stottis.com/bulkxml instead of http://schemas.microsoft.com/SQLBulkXML


Once that was changed (I actually changed it in the registry and in the adm_Adapter table, it started working as expected:


So, it makes sense, what happens is that the BizTalk port, after the pipeline is complete, it then reads the configuration, and attaches the particular send port configuration to the message and sends the message to the appropriate dll that is defined in the adm_Adapter table, that adapter dll, then extracts the configuration and sends it off as is is programmed.


This is probably common knowledge, but I have been too busy making every flavor of mistakes that can be made, to look at the core functionality of how BizTalk actually works.

Hope that this explains what is going on a little bit.

Jan 042009

I created a SQL Bulk load adapter as the standard SQL Adapter casts the stream to a string and runs out of memory for large datasets.

While developing the Bulk Load adapter, I need to specify the connection information. I was about to implement a custom interface, I took the lead from Tomas Restrepo’s Null Adapter. I was having some problems because I was developing on a 64bit box, and then discovered this information about implementing the adapter on 64 bit machines.

I thought that there must be an easier way to do this!

There is an undocumented schema annotation that allows to add connection information.

<xs:element name="ConnectionString" type="xs:string"> <xs:annotation> <xs:appinfo> <baf:designer> <baf:displayname _locID="ConnectionStringName">Edit this field in the resource file</baf:displayname> <baf:description _locID="ConnectionStringDesc">Edit this field in the resource file</baf:description> <baf:editor assembly="%BTSROOT%\Microsoft.BizTalk.Adapter.SQLAdmin.dll">Microsoft.BizTalk.Adapter.SQLAdmin.DatalinkUITypeEditor</baf:editor> <baf:converter assembly="%BTSROOT%\Microsoft.BizTalk.Adapter.SQLAdmin.dll">Microsoft.BizTalk.Adapter.SQLAdmin.DatalinkTypeConverter</baf:converter> <baf:category _locID="DatabaseGrp">Edit this field in the resource file</baf:category> </baf:designer> </xs:appinfo> </xs:annotation> </xs:element>

Here is the adapter interface:


Also, I wanted to show the database information, how you have to do it in C# is the following:

OleDbConnection connection = new OleDbConnection(valueOfConnection); if (connection.DataSource.Length != 0) uri.InnerText = "SQL://" + connection.DataSource + "/" + connection.Database; else uri.InnerText = "SQL://" + connection.DataSource + "/";

Which shows the following:


Jan 032009

In an effort to standardize the consumption of EDI documents, click what I have come across is the same pattern.

  1. Create flat file schema
  2. Bring in and EDI Transaction
  3. Create the mapping to a fixed length flat file.
  4. Deploy
  5. Start Testing

This is pretty standard, sick and there is nothing wrong with this approach, it is pretty easy, and relatively quick to implement.The problem is that when the backend changes, the business now wants different data, or they come back and inform that something is missing. The normal request is “we need to be getting field X, can you run the past {month/year} data?”

So what that means is the following pattern must me followed:

  1. Modify flat file schema
  2. Modify existing map
  3. Undeploy map project
  4. Redeploy modified flat file project
  5. Redeploy modified mapping project
  6. Apply bindings
  7. Run all files through updated process

This becomes very tedious when either the output is not clearly defined, or there are a lot of transactions to process.

What we have created is a EDI warehouse that stores all EDI data. Below is the data flow: (click to enlarge)

Warehouse Process

Once the data is loaded with it’s subsequent primary keys, it waits until the next transaction in the interchange is processed. Once the transactions have been processed, it calls a stored procedure that will extract the data to whatever the business needs.

The nice part of this design, is that when the business needs different data, simply changing the query is all that is now needed. No more code changes, redeploys, etc.


Items that come with the EDI Warehouse package:

  • Because the SQL Adapter runs out of memory for large transactions, a custom Bulk Load adapter is included
  • The following transactions are included: 820, 850, and 856 which include the following:
  • Table definitions for each transaction, map into warehouse, mapping schema for load process

Here is a snapshot of some of the tables created for a 271 project that has been in production for 4 months.

Database Structure

Any ANSI X12 transaction can be purchased for the prices on the Products/Services page.