From time to time our pre-sales and tech support teams receive questions on how to take XML messages created by the Spread for BizTalk disassembler and break the single message into individual record item messages - a task that in BizTalk Server lingo is refered to as "debatching."
The Spread for BizTalk disassembler parses incoming spreadsheet data (xls, xlsx, csv, txt) into XML-data as a single BizTalk Message. The first approach I considered was to add an XML disassembler following the Spread for BizTalk disassembler in a single receive pipeline. Good concept, but the logic does not hold water because the XML produced by Spread for BizTalk is typed to a particular schema that is not an envelope schema, and to use the XML disassembler as part of a debatching solution, an envelope schema must be used.
So the next iteration was to create an envelope schema and map the Spread for BizTalk XML to that schema. Using the XML disassembler to crack the envelope was still the approach I wanted to use, so I created another receive pipeline that contains the XML disassembler, and created an orchestration to first map the XML data (message) then invoke the receive pipeline that contains the XML disassembler. Following this, the orchestration simply loops through the output messages.
As in life, and in development, there are many pathways to get from A to B. This is not the only way to solve the this debatch task in BizTalk Server nor is it necessarily the best way, but it does work well, and is easy to follow and maintain.
A big thanks to blog postings from Stephen Thomas http://geekswithblogs.net/sthomas/archive/2005/06/16/44023.aspx Gaurang Desai http://www.codeproject.com/KB/biztalk/BizEnvelop.aspx.
The source code for this sample is attached below as "debatch.zip".