Share via


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 


See Also