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.
|
1 2 3 4 |
Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices IntegrationServices; Microsoft.SqlServer.Management.IntegrationServices.Catalog Catalog; Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder CatalogFolder; Microsoft.SqlServer.Management.IntegrationServices.PackageInfo Package; |
2. Connect to the SSIS catalog using a connection to msdb. Point to the Catalog and Folder where the packages have been deployed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
string SSISConnectionString; string FolderName; string ProjectName; string ServerName; string PackageName; //Set up variables in the package to store these values. ServerName = Dts.Variables[“SSISServerName”].Value.ToString(); FolderName = Dts.Variables[“SSISFolderName”].Value.ToString(); //SSISTempale ProjectName = Dts.Variables[“SSISProjectName”].Value.ToString(); //SampleSSIS PackageName = Dts.Variables[“SSISLoadPackageName”].Value.ToString(); SSISConnectionString = string.Format(“Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;”, ServerName); IntegrationServices = new Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices(new System.Data.SqlClient.SqlConnection(SSISConnectionString)); Catalog = IntegrationServices.Catalogs[“SSISDB”]; // Find the catalog folder. Dimensions in your example CatalogFolder = Catalog.Folders[FolderName]; // Find the package in the project folder Package = CatalogFolder.Projects[ProjectName].Packages[PackageName]; |
3. Set up any parameters that you will pass the child package. You will set up this code block for each parameter.
|
1 2 3 4 5 6 7 |
//Set the cnfParentExecutionLogID Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet parmParentPacakgeID = new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = Package.Parameters[“cnfParentExecutionLogID”].ObjectType, //Get the Type from the existing parameter ParameterName = “cnfParentExecutionLogID”, ParameterValue = Dts.Variables[“cnfExecutionLogID”].Value }; |
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.
|
1 2 3 4 5 6 7 8 |
//By default the Execute is Asynchronous. Set the SYNCHRONIZED Boolean parameter to update this value //0 – Asynchronous; 1 – Synchronized Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet parmSynchronized = new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = “SYNCHRONIZED”, ParameterValue = 0 }; |
5. Add each parameter to a parameter set.
|
1 2 3 4 |
//Add Parameters to setValueParameters var setValueParameters = new System.Collections.ObjectModel.Collection(); setValueParameters.Add(parmParentPacakgeID); setValueParameters.Add(parmSynchronized); |
6. Execute the package and return the SSIS Catalog Execution ID. This execution_id will join to the SSISDB.catalog.executions
|
1 2 |
string ExecutionId = "" ExecutionId = Package.Execute(false, null, setValueParameters ).ToString(); |
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.
|
1 |
SELECT Status, execution_id FROM catalog.executions E WITH (NOLOCK) WHERE execution_id = @execution_id |