Execute SSIS 2012 Package with Parameters via .Net
The parameter examples in BOL are a little compact. Here is how you add system, project and package parameters when executing an SSIS package from the SSIS Catalog via .Net. For more details (references/assemblies, usings/imports, etc.) see this blog post.
C#
// Connection to the database server where the packages are located
SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;");
// SSIS server object with connection
IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
// The reference to the package which you want to execute
PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["SSISJOOST"].Projects["MyProject"].Packages["MyPackage.dtsx"];
// Add a parameter collection for 'system' parameters (ObjectType = 50), package parameters (ObjectType = 30) and project parameters (ObjectType = 20)
Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();
// Add execution parameter (value) to override the default asynchronized execution. If you leave this out the package is executed asynchronized
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });
// Add execution parameter (value) to override the default logging level (0=None, 1=Basic, 2=Performance, 3=Verbose)
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 3 });
// Add a project parameter (value) to fill a project parameter
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 20, ParameterName = "MyProjectParameter", ParameterValue = "some value" });
// Add a project package (value) to fill a package parameter
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "MyPackageParameter", ParameterValue = "some value" });
// Get the identifier of the execution to get the log
long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);
// Loop through the log and do something with it like adding to a listbox
foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
{
SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message);
}
**
**
VB.Net
**
**
' Connection to the database server where the packages are located
Dim ssisConnection As New SqlConnection("Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;")
' SSIS server object with connection
Dim ssisServer As New IntegrationServices(ssisConnection)
' The reference to the package which you want to execute
Dim ssisPackage As PackageInfo = ssisServer.Catalogs("SSISDB").Folders("SSISJOOST").Projects("MyProject").Packages("MyPackage.dtsx")
' Add a parameter collection for 'system' parameters (ObjectType = 50), package parameters (ObjectType = 30) and project parameters (ObjectType = 20)
Dim executionParameters As New Collection(Of PackageInfo.ExecutionValueParameterSet)
' Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
Dim executionParameter1 As New PackageInfo.ExecutionValueParameterSet
executionParameter1.ObjectType = 50
executionParameter1.ParameterName = "SYNCHRONIZED"
executionParameter1.ParameterValue = 1
executionParameters.Add(executionParameter1)
' Add execution parameter (value) to override the default logging level (0=None, 1=Basic, 2=Performance, 3=Verbose)
Dim executionParameter2 As New PackageInfo.ExecutionValueParameterSet
executionParameter2.ObjectType = 50
executionParameter2.ParameterName = "LOGGING_LEVEL"
executionParameter2.ParameterValue = 3
executionParameters.Add(executionParameter2)
' Add a project parameter (value) to fill a project parameter
Dim executionParameter3 As New PackageInfo.ExecutionValueParameterSet
executionParameter3.ObjectType = 20
executionParameter3.ParameterName = "MyProjectParameter"
executionParameter3.ParameterValue = "some value1"
executionParameters.Add(executionParameter3)
' Add a project package (value) to fill a package parameter
Dim executionParameter4 As New PackageInfo.ExecutionValueParameterSet
executionParameter4.ObjectType = 30
executionParameter4.ParameterName = "MyPackageParameter"
executionParameter4.ParameterValue = "some value2"
executionParameters.Add(executionParameter4)
' Get the identifier of the execution to get the log
Dim executionIdentifier As Long = ssisPackage.Execute(False, Nothing, executionParameters)
' Loop through the log and do something with it like adding to a listbox
For Each message As OperationMessage In ssisServer.Catalogs("SSISDB").Executions(executionIdentifier).Messages
SSISMessagesListBox.Items.Add(message.MessageType.ToString() + ": " + message.Message)
Next
Example:
http://2.bp.blogspot.com/-gbPrWf_Srqw/UNyAzNYtmBI/AAAAAAAABs4/I99B6vlt5Ug/s1600/CallSSIS2012Package04.jpg