Using Variables as Input and Output in SSIS Execute SQL Task

This tutorial walks you through the steps to use variables as Input variables and Output variables in Execute SQL Task within SQL Server Integration Services ( SSIS ).

STEP 1:
Say you want to create a Stored Procedure, which uses as Input two integer variables VAR1 and VAR2 and gives you output VAR3 which is sum of VAR1 and VAR2.

CREATE PROC usp_getSum @VAR1 INT, @VAR2 INT, @VAR3 INT OUTPUT
AS
BEGIN
           SET @VAR3 = @VAR1 + @VAR2      
END

STEP 2:
Now you want to use this stored procedure in SSIS Execute SQL Task, with variables variable1 and variable2 as input, while variable3 as output.

• Create a new SSIS Package and add Execute SQL Task to it.
• Now add variables by right clicking anywhere on the package. Add three variables as shown

• Now select the appropriate OLEDB connection and enter SQL Statement as below.

EXEC usp_getSum ?,?,? OUTPUT
Since we will be using variables as input and output we have provided with Question mark in those                   respective fields. This has been shown in below figure.

• Next Step is mapping the parameters. Click Parameter Mapping and add three parameters. Select the user variables that we have created and Select Variable1 and Variable2 as Input and Variable3 as Output. Also give them parameter names as 0,1 and 2 respectively. Parameter names are actually the order in which Question Marks (?) will be replaced by variables. Select appropriate Data types to be used. Since all the three data types we are using are integers we have used LONG here.

Thats it the package is ready to run. But wait how will you see the results.

So to view the results we will create a table and insert value of these variables in them.

CREATE TABLE tempTable(

VAR1 INT, VAR2 INT, VAR3 INT

)

Next step is inserting value of those variables in this table. So we will use another Execute SQL Task to write these variables.

We will use the SQL Statement:    INSERT INTO tempTable VALUES(?,?,?)

Again do the parameter mapping and use Variable1, Variable2 and Variable3 as Input this time.

That’s it. It is done. Now what you just need is provide values for Variable1 and Variable2 and see the results in tempTable.

Thats it. Run the package and see the values in SSMS.