SSIS and Stored Procedure OUTPUT Parameters

[Originally posted by] : http://www.sqlsafety.com/?p=649

SSIS and Stored Procedure OUTPUT Parameters

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:

 

USE AdventureWorks2012;
GO

CREATE PROC ssisoutput
@count INT OUTPUT
AS
SELECT @count = COUNT(*)
FROM Person.Person;
GO

BACKGROUND

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.

PACKAGE

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:

imageLeave the SQLSourceType set to DirectInput and in the SQLStatement type the following query:

DECLARE @countout INT
EXEC ssisoutput @count = @countout OUTPUT
SELECT ? = @countout

image

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:

 

imageNotice 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

imageThis is obviously a bit more efficient as there is no need for variable declaration and assignment.

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.

imageIn the SQLStatement property put in ONLY the stored procedure name ssisoutput, DO NOT INCLUDE EXEC, and configure the IsQueryStoreProcedure property to True.

image

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.

image

Additional information on parameters in Execute SQL tasks can be found on Technet.  The sample package reference in this post can be downloaded here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s