Step by step of executing SSIS 2012 package through stored procedure

  • Article

Many times we want to run SSIS package programmatically and want to pass parameter through it. Sounds similar!!! People using this approach so what’s new about it? In SSIS 2012, we have option to deploy SSIS package in Integration Services server which is an instance of SQL Server database engine. Please refer msdn article on Integration Services (SSIS) Server here for more information.

In this article, I am going to show one of the ways on how we can execute SSIS package and provide parameter value using stored procedure. The stored procedure I used get setup once you setup SSIS server.  

1)      Open SQL Server data tools

2)      Create a new SSIS Project

3)      Goto parameters tab and click “Add Parameter”

          

4)      Create a parameter and name it “Parameter1”, Data type “String”, put a test value and set “Sensitive” and “Required” value (optional for this demo)

5)      Go to “Control Flow”, drag and drop “Script task”

6)      Set “ReadOnlyVariables” as mentioned below

         

 

7)      Click on “Edit Script” and it will open programming environment

        

 

8)      Just below the Dts.TaskResult….. in Main() add an message box. Mentioned above.

9)      Go to “Solution Explorer” and deploy the package

        

 

10)   Deploy the package in Integration services server. Mentioned below. If you are not sure how to setup integration services server please refer here. Easy way to create the SSIS catalog is through SQL Server management Studio.

       

 

11)   Once deploy, Connect to SQL Server management. Below is the screenshot how your package looks like.

       

 

12)   Open a new query window

13)   Create a variable type of bigint to hold execution id.  

14)   Call create_execution stored procedure. It creates an instance of execution in SSIS catalog. It will take parameters 1) Folder Name 2) Project Name 3) Package Name 4) Refence Id 5) use32runtime and execution id as output parameter.

15)   Call set_execution_parameter_value stored procedure. It sets value of a parameter for an instance of execution in the SSIS catalog.

16)   Finally call [SSISDB].[catalog].[start_execution] stored procedure to start the execution. It will take execution id as parameter. Below is the screen shot of step from 13 to 16.

       

17)   Execute the query. Below is the output

       

18)   Let’s change the parameter value and run it again

       

 

19)   We can use any .net application to run SSIS package using the above method.

20)    Below you will find the detailed information of the stored procedure I have used in this article.

  1. catalog.create_execution
  2. catalog.set_execution_parameter_value

Comments

  • Anonymous
    June 15, 2017
    Amazing article! It works perfect. I would comment about the TargetServerVersion property in the Project Property Pages that it should be set as SQL Server 2014 (or SQL Server 2016) version due to script task control incompatibility in SSIS running in SQL Server 2016, by default, this property is set to SQL Server vNext.Thanks for sharing this example.Regards,
    • Anonymous
      January 02, 2018
      Thanks Cesar Arronte for the feedback and point put the change required.