I was recently asked to help configure an execute SQL task that would call a stored procedure and capture an output parameter in an SSIS variable. Although I had done this before and it seems straight forward it took me quite some time, as well as opening up most every package I have ever created to find an example, to complete this. Hopefully this post will save others time, as well as myself.
To demonstrate this first create a stored procedure in the AdventureWorks2012 database that uses an OUTPUT parameter:
CREATE PROC ssisoutput
@count INT OUTPUT
SELECT @count = COUNT(*)
This is obviously a simplified example so to provide a better context let me describe the task I was confronted with. The stored procedure that was called was running several transactions, inserts and updates, and the output parameters were recording the number of records that were affected. The output parameters were then captured in SSIS parameters that were used to dynamically generate a send mail message that notified all operators of the number of records.
Create a package called OutPutParameters. Within the package create a variable called countout with a data type of Int16 and a package level scope
METHOD 1 OLEDB DECLARE
From within an SSIS package drag and drop an Execute SQL task onto the control flow design pane. In the Execute SQL task configure the connection to use the instance where the AdventureWorks2012 database containing the stored proc resides:
DECLARE @countout INT
EXEC ssisoutput @count = @countout OUTPUT
SELECT ? = @countout
The above T-SQL statement first declares a variable, @countout, that will be used to hold the output parameter value. The stored procedure is executed and the output parameter value, @count, is passed to the variable @countout specifying OUTPUT.
With the connection and statement being configured now configure the Parameter Mappings of the task mapping the countout variable to the output parameter:
Notice that the “?” in the SQLStatement acts a a placeholder for the parameter. The Parameter Name uses the 0 based indexed value of the parameter placeholder. Since there is only one one parameter placeholder in the query, “?”, the value is set to 0.
METHOD 2 OLEDB ASSIGNMENT
This method is almost identical except that the SQLStatement does not declare a variable, but rather directly assigns the output parameter to the parameter placeholder. The ONLY difference is the SQLStatement:
EXEC ssisoutput @count = ? OUTPUT
METHOD 3 ADO.NET
The question often comes up in regards to being able to call a parameter by name rather than indexed ordinal position as it appears in the SQLStatement property. Unfortunately this is only possible using an ADO.NET connection manager.
Drag and drop another execute SQL task onto the control flow design pane and configure it to use a new ADO.NET connection manager.
In the Parameter Mappings window map the countout variable to the output parameter and use the Parameter Name @count, the name that is defined in the stored procedure.