Home > Uncategorized > Execute Stored Procedure with Input and Output Parameters and Return Status in SSIS

Execute Stored Procedure with Input and Output Parameters and Return Status in SSIS

In this blog post, I would like to show a simple example on how to handle or use the stored procedure that has  input , output parameter and return status value in SSIS.

Create Stored Procedure in SQL Server

CREATE PROCEDURE usp_TestProcedure
    @MyInput varchar(100),
    @MyOutput varchar(100) OUTPUT
    SET @MyOutput = ‘SQL Server Rider’;
    RETURN 5;

This user procedure has two parameters input and output. I also returns a return status. All these parameters and return values are imaginary and has no purpose.

Execute usp_TestProcedure in Query window

EXEC [dbo].[usp_TestProcedure] ‘Blogger’, @Output OUTPUT
PRINT @Output

@Output is the character variable and also act as an output parameter variable to bring back the value of the @MyOutput parameter in the stored procedure.

To execute the same stored procedure in the SSIS, we are going to use Execute SQL Task. So, We need to create three variables in the package to execute this stored procedure.

Step 0: Create SSIS solution in SS

Step 1: Add 3 package variables with correct data type.


Step 2: Add and Configure the SQL Execute Tast


Configure Database connection, Stored procedure and its parameters.


SQL Statement: exec ? = usp_TestProcedure ?,? OUTPUT

Map variables to Input/output parameters and return value .


Step 3: Executing the package and watch the variable’s value during run-time.


Package execution succeeded by calling the stored procedure.

Common mistake

We need to Map the parameters in sequential order. It means, in this example parameter 0 is return value and 1 is the input and 2 is the output parameter.

Categories: Uncategorized
  1. Kumar
    September 18, 2015 at 1:14 pm

    Ayyapan, The most I liked in this article is Common mistake section. That really helped and I guess no one mentioned in any of the articles I have read. Thanks.

  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: