Comments on the BizTalk samples

May 20, 2010 at 4:05 PM

While the samples are interesting, it might be interesting to take a different spin on them.   I have seen many cases where BizTalk developers want to do some form of bulk ETL work within a larger BizTalk process.   Either in the form of a bulk select from SQL (and other data sources), or bulk insert.   SSIS shines in this area, BizTalk does not.   Yet, they want to leverage BizTalk for its Accelerators, Adapters, and end-to-end tracking.

What I think we are missing from these samples, are handling bulk files with SSIS:

Bulk Insert - Sample 2 appears to be close to handling a bulk insert, but won't the approach of passing the message via a SSIS variable choke on the size of the data passed?  So it would be interesting to instead, write the stream to a file, and pass the UNC path of the file to the variable, and let the SSIS package consume the file.  The component would then optionally pass on a status message (including the UNC path) to the MessageBox.  Developers could then use that (small) status message as they saw fit.  Let me know if I am missing something on the SSIS variable size.

Bulk Select - This might be in the form of a BizTalk Receive Adapter that executes a package to do a Select.  The package writes data out to a file, and passes a status message (with path to the output file) on to the receive pipeline.   A corresponding pipeline component could then be used in the Send pipeline to read the file path from the message, and then replace the message with the file contents in the outbound stream for sending via any adapter