Input, Output and Return Values in SSIS Execute Sql Task

Friends,

In this post we are gonna discuss about all the THREE Parameter directions INPUT,OUTPUT and RETURN VALUE in Execute Sql Task. I am pretty sure that most of the SSIS developers might have used this task many times but at least few of them still not clear about how to use the three directions of Parameter and this post is for that set of people.

I can explain the INPUT direction by using any Sql Statement but to explain all the three Stored Procedure is the best option to choose. So, in this post I am gonna use SP to demonstrate the same.

I have created a damn simple SP with one parameter to demonstrate INPUT parameter and you can see the same in the below given SC.

Here I created a Stored Procedure “test” with a  Input Parameter @PrdKey

Now let’s see how to call the same in the Execute Sql Task.

1)     Drag and drop an Execute Sql Task to control flow and double click or right click and select Edit to set the properties.

2)     Once the properties wizard opens then set the connection to the database and use the following query for Sql Statement.

Exec test ?

3)     The ? in the above query means, it is a parameter for which the value will be assigned at runtime using variable.

4)     Now go to the Parameter Mapping page to map the parameter (variable) which will provide value to the “?” provided in the query.

5)     Under variable name select the variable which provides value to the query parameter.

6)     Under direction we will have THREE options and select INPUT as we are inputting the value to the parameter.

7)     Under datatype select the appropriate datatype and in this case I have selected LONG as the parameter for the SP.

8)    Under parameter Name property set 0 (Zero) as we have only one parameter in our query.

The parameter name values will be as given below –

? –> 0

?,? –> 0,1

?,?,? –> 0,1,2

The parameter name will be interger values starting from ZERO and it will be mapped in such a way that first ? will be replaced with first parameter (parameter name 0) and second one with second parameter ( parameter name 1) ….

9)     Now select OK and Execute.

10)  Now I created a SP with all the three values i.e Input Parameter, Output Parameter and also RETURN Value as shown below.

11)   Here @ProductKey is input , @ProductName is Output and “16” is Return Value parameter created stored procedure. Remember that SP can return ONLY Integer values.

12)  Now the query for this SP will be like given below in Execute Sql Task –

Exec ? = Test_sp ? , ? OUTPUT  

13) Here first ? is Return Value parameter, second ‘?’ Is Input parameter and third ‘?’ with OUTPUT keyword is output parameter

14)   Now Open Parameter Mapping and create Variable and Map it to the ‘?’ as explained above Section. Remember that the parameters assigning should follow sequence and in our example first one should be RETURN VALUE Parameter then INPUT and finally OUTPUT.

INPUT – Used for Input variables. The value will be passed from calling environment.

OUTPUT – Used for Output variable and value will be returned to calling environment.

RETURN VALUE – The value which is returned by SP.

15)  Here I created three variable Count for returnValue ,MinValue for Input and ProductName for the output and order them as 0,1,2 as per there occurrence.

16)  Click Ok and now we can pass values to the variables in the Variable window and run the package.

Note – We can configure OUTPUR (Result set) to return Table/XML/Single Row which will be discussed in next post.

Thanks a lot to Swati Srivastava for providing most of the content and SC to draft this post.

Regards,
Roopesh Babu V