Stored Procedure Return Values and Output Parameters in SSIS

Being a long-time SQL Server user, I have written a lot of stored procedures and made full use of input and output parameters as well as return values.  When I started learning and working with SSIS, one of the first things I wanted to know was how to call and get results from stored procedures.  Mike Davis has a blog posting on calling stored procedures and passing input parameters so I thought I would address return values and output parameters.

Here’s a simple stored procedure that has one input parameter,  two output parameters and a return value.

CREATE PROCEDURE dbo.uspGetCustomerNameAndOrderCount
(
    @CustomerID int,
    @CustomerName nvarchar(101) output,
    @OrderCount int output
)
AS
BEGIN
    SET NOCOUNT ON;
    SET @CustomerName = 'Red Bicycle Company';
    SET @OrderCount = 1375;
    RETURN 100;
END;
GO

To call this procedure, first add an Execute SQL Task to a package in SSIS and edit its properties.  Under the General properties, set the SQLStatement to the following for an OLE DB connecion type.

EXEC ? = dbo.uspGetCustomerNameAndOrderCount ?, ? OUTPUT, ? OUTPUT

Notice the question marks in the statement.  These represent the return value and parameters of the stored procedure.  The SQL Statement section of the editor should look like the following.

Next, select the Parameter Mapping properties and create or select a variable for the return value and each parameter of the stored procedure.  Make sure you select the appropriate Direction and Data Type and change the Parameter Name to the ordinal position of the return value or parameter.  For example, the variable for the return value is listed first with a Parameter Name of 0.  When finished, the parameter mappings should look like the following.

Close the Execute SQL Task editor and run the package.  I added a Script Task to my package to display the return value and output parameters in a message box.

The values displayed are the return value, CustomerID, CustomerName and OrderCount in that order.  All values were returned from the stored procedure except the CustomerID which was defined when the variable was created.

This example uses an OLE DB connection.  Other connection types such as ADO.NET and ODBC require slightly different syntax in the SQL Statement.  For more information, see this topic in books online.