Output 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

Let’s see how we can use an output parameter in SP.

I am creating a simple SP with an output parameter.

Create Procedure P_Insert_Emp (@E_name varchar(50),  @E_mobile varchar(10),  @E_add   varchar(10),  @Number_of_Records int output ) AS Begin    insert into emp (E_name,E_mobile,E_add)    values (@E_name,@E_mobile,@E_add)    Select @Number_of_Records=Count(*) from emp End;

In this SP I am inserting a record in each run and getting the count of all records in the output parameter.

I am taking execute SQL task and creating the connection manager. For the input and output parameter, we need to create variables to store the value.

 

Before that, I am creating the variable to store the values

 

Now we go the parameter mapping.

 

Keep in mind for the output parameter we need to select Direction as Output.

Click ok.

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

 

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

Now the package is ready to execute

 

Before running this package records in the table.

3 records.

Now I am running this package

See the records in the table.