Aggregating the "Split-Gatherer" Pattern using SQL

One of the limitations of BTS 2004 as compared to BTS2006 is its inability to “gather” the transactions it has split from the original file. “Splitting” can be achieved by combination of “Document” and “Envelope” schemas. In my present project, we needed to split the file and then add the “split” transactions on file -by- file basis. Presently, it is achieved by the “Convoy” design. This involves using “Correlation” dataset that waits for the field that is “correlated” (FileName in our case). It then adds all the “split” transactions for that file. Since we rely on “serial” processing as compared to “parallel” processing that orchestrations are intended for, this is not an optimum design. The major drawbacks of this approach is 1) It leads to “zombie” scenario in Biztalk. 2) Performance declines exponentially as the transaction volume increases.

These 2 scenarios were slowly taking menacing proportions. In order to counteract them our team came up with “SQL database” aggregator. It is simple in design and very fast. The files are split as before. But now each transaction is stored in SQL database as XML node. Parameters like Orchestration ID, FileName and Unique identifiers are passed along with the XML node. At the end the XML file is extracted by combining these nodes and using parameters passed to aggregate the correct transactions from the database. Stored procedures are used to Build the final XML .