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
AS
BEGIN
    SET NOCOUNT ON;
    SET @MyOutput = ‘SQL Server Rider’;
    RETURN 5;
END
GO

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

DECLARE @Output AS VARCHAR(100)
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.

pic1

Step 2: Add and Configure the SQL Execute Tast

pic2

Configure Database connection, Stored procedure and its parameters.

pic3

SQL Statement: exec ? = usp_TestProcedure ?,? OUTPUT

Map variables to Input/output parameters and return value .

pic4

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

pic5

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.

Advertisements
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 )

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

%d bloggers like this: