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
@MyOutput varchar(100) OUTPUT
SET NOCOUNT ON;
SET @MyOutput = ‘SQL Server Rider’;
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
@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.
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.