Calling SSIS from BizTalk Orchestration

I have a scenario were I need to move a vast amount of data, and I need to use BizTalk to control the flow and contain the business logi开发者_运维问答c. The problem is that BizTalk will not be able to handle the amount of data that needs to be moved.

We have decided to a BizTalk Orchestration to kick off an SSIS package that does the actual heavy lifting. However, there is a caveat in that we have to be able to pass information into SSIS such as file location and info about how to split certain data up.

My question is, what is the best way to call into SSIS from an Orchestration given those parameters? Should I build a webservice around it? Is there an adapter or stored procedure that I can call? Or is there a way to call it directly from the Orchestration?

You may want to simply add a C# class with a static method to your project and call the standard code to kick off an SSIS package:

http://www.codeproject.com/KB/database/CallSSISFromCSharp.aspx

http://msdn.microsoft.com/en-us/library/ms136090.aspx

Simply call the method from your orchestration.

We ended up needing to pass variables to the SSIS package in order to have it correlate back to the orchestration that had launched it. Also we needed to use the same orchestration but have it launch different SSIS packages based on the receive location.

We ended up with a solution like this:

1. Orchestration calls a Stored Procedure with two parameters: correlation GUID, and receive location.
2. Stored procedure stores needed values in SSIS config table.
3. Stored procedure calls SQL server Agent Job as appropriate
4. Job runs SSIS job
5. SSIS Job finishes successfully or has errors and writes the result to an XML file with the correlation GUID.
6. BizTalk picks up the XML file and correlates on the GUID.
7. BizTalk handles any error handling as need.

Our colleague has written an much more detailed explanation of this solution here, for anyone who is interested.