Implementing a batch system in BizTalk in order to keep two applications/systems in sync.

Setting the Context

Batch applications are still critical in most organizations in large part because many common business processes are susceptible to batch processing. There are certain scenarios when one would need to design a batch system, in order to handle and/or control the way he data flows into a system.

Even with advances in batch program development, problems with batch performance are still very common. This is particularly painful during implementations. Operations team needs to ensure that the batch window is not breached, to guarantee their Service-Level Agreement (SLA) is met.

Batch processing is also used for efficient bulk database updates and automated transaction processing, as contrasted to interactive online transaction processing (OLTP) applications. This article attempts to explain the implementation of a non trivial batch system using BizTalk.

Phase - I - Building the Batch system

A batch generally consists of a batch table and a configuration table. The batch table would contain the primary key of the record and a status flag. The batch table holds the status of the record in the batch. The batch is considered complete when all records are either in SUCCESS or FAILED status. The following is the list of status values used in the batch.

Step 1: Creating the Batch table

The batch table consists of a unique key to identify a batch record and a related status with it. In this case we are trying to create a batch table for an Account CRM entity as an example. A tiny database with few tables are created on the CRM system. See the figure for a clear understanding of it.

Step 2: Creating the Configuration table

The Configuration table, in this case Batch Config consists of a key which holds the last batch run date/time. This would prevent the re-run of a batch record to avoid duplicates.

Step 3: Building the SQL Batch Job

The SQL batch job would fill in the batch table with the relevant records matching a given criteria. The sample script for the batch job is shown.

Phase - II - Configuring BizTalk to process the Batch

Step 1: BizTalk Receive location

The BizTalk receive location can be used to pull in the batch into BizTalk. The BizTalk receive location configuration is shown below.

Step 2: BizTalk Orchestration

Once a batch record is within BizTalk, an orchestration can be written to process the record and perform various operations on it. The Batch orchestration code is beyond the scope of this article.

Step 3: BizTalk Status Flow

The status flow lifecycle is shown in the figure. The initial status is LOADED and this could end with either a SUCCESS or FAILURE. Custom logic can be written within the Orchestration to move the status to DEFERRED.

Step 4: System Architecture

The overall system architecture is depicted in the diagram below. Note that for pushing data into CRM, you might want to use the CRM web service using the BizTalk WCF adapter.

Phase III - Further action items

As a developer and designer of BizTalk orchestrations, you must determine what additional actions are required to be taken by the orchestration. This could involve the following

• Posting a message to a Web Service.
• Sending an email or an Acknowledgement.
• Updating a record in a table.

Note that a similar set of actions are required for the two-way sync process.

Further references on Batch processing

• Batching EDI Data in BizTalk Server 2010
• Batching Tutorial for using Microsoft BizTalk 2010 Accelerator for HL7 (BTAHL7)
• This is an old article, but conceptually worthwhile to read BizTalk Aggregation Pattern for Large Batches