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.