Search code examples
biztalkbiztalk-2009

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 logic. 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?

Solution

  • 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.