Simulate Batch Processing in BizTalk Using a Database and a Service Window

On a recent project I had a need to simulate a batch aggregation process using BizTalk. In this scenario, the source system would produce data files. Each file would represent one business transaction. The destination system, however, was expecting to receive all of the transactions in a single file. There is more than solution to this scenario. For example I could have built a parallel convoy but in this case that seemed like a lot of effort based on the requirements and the volume of data that will be passing through this interface. Instead I was able to “simulate” a batching process using a simple table in SQL Server and a BizTalk service window.

The first step is to create a staging table in a SQL Server database. In my case the destination system was receiving a flat-file with one row representing one business transaction so I modeled my database table using a similar structure.

The process consists of two message flows. The first process picks up the data files from the source system and inserts the data into the staging table. The second process then reads the rows from the staging table into a single message and then maps them to the flat-file for the destination system. The key item is to configure the SQL receive location with a service window that does not overlap with the source system’s schedule for creating files. The idea is to ensure that all of the source files have been processed and committed to the staging table before the second process queries the database. For example, if the source system is configured to create data files at 5:00pm, you may want to configure the SQL receive location service window to process files between 6:00pm and 6:10pm. If the source system is going to run on a continuous schedule you may want to configure a service window on the first process too. Just make sure that the two service windows do not overlap.

I found this to be an easier way to aggregate messages using BizTalk without introducing the complexity of a parallel convoy. Plus with a convoy there is always the danger that one or more messages may not arrive in a timely manner and cause zombie instances to show up in the suspended message list.