ReturnValue direction in execute sql task in SSIS

For executing the SPs we are using Execute sql task.

Read: CallStored Procedure in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/call-stored-procedure-in-execute-sql.html

Read: Outputdirection in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/output-direction-in-execute-sql-task-in.html

Let’s see how we can receive the SP return value in execute task.

Here I am creating a simple SP in this we are returning the current identity value. E_ID is identity column.

Create Procedure dbo.P_Insert_Emp (@E_name varchar(50),  @E_mobile varchar(10),  @E_add   varchar(10),  @Number_of_Records int output ) AS Begin declare @r int;    insert into emp (E_name,E_mobile,E_add)    values (@E_name,@E_mobile,@E_add)    select @r=@@IDENTITY    SElect @Number_of_Records=Count(*) from emp    return @r  End;

Now I am taking the execute sql task and doing the configuration.

Below is the sql statement

exec ?=P_Insert_Emp ?,?,?,? OUTPUT

Before that I am creating the variable to store the values

Now go to the parameter mapping.

Keep in mind for receiving the return value we need to select Direction as ReturnValue.

Click ok.

To see the value of the output parameter I am taking script task.

  

In C# script I am writing below code to see the message.

 

Now package is read to execute

Before running this package records in the table.

 

6 records.

Now I am running this package

See the records in the table.