How To Execute A SSIS Package Within A SSIS Script Task

By: Matt Wollner

Senior Business Intelligence Consultant at Key2 Consulting

 

I was looking to kick off multiple SSIS packages, in parallel, from a master SSIS package. The most common way I’ve seen this done is to create several Execute Package tasks. I wanted to move this logic to a script task. Kicking off an SSIS package from within a Script task ended up being harder than I thought.

For this example, I will execute an SSIS package that has been deployed to the SSIS Catalog using a C# script task. After I was able to execute a package, I created an array of package parameters and looped through the execute of each of the packages that I needed to execute. That is not part of this sample.

1. Add a reference to the SSIS namespaces.

2. Connect to the SSIS catalog using a connection to msdb. Point to the Catalog and Folder where the packages have been deployed.

 

3. Set up any parameters that you will pass the child package. You will set up this code block for each parameter.

4. Decide whether you want the package to run Synchronously or Asynchronously. By default, the Execute is Asynchronous. Set the SYNCHRONIZED Boolean parameter to update this value.

5. Add each parameter to a parameter set.

6. Execute the package and return the SSIS Catalog Execution ID. This execution_id will join to the SSISDB.catalog.executions

After the package has been kicked off I attempted to use the internal status properties. I didn’t have much luck with these. My main goal was to kick off multiple package asynchronously. When I tried to access the catalogSSISDB.Executions[ExecutionId].Status, the package would wait until it completed before returning a result. That kinda defeats the purpose of running asynchronously. Instead I queried the SSISDB.catalog.executions directly.

Leave a Reply

Your email address will not be published.