Many times we want to run SSIS package programmatically and want to pass parameter through it. In SSIS 2012, we have option to deploy SSIS package in Integration Services server which is an instance of SQL Server database engine. and call it witha stored procedure. 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 procedure is :
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