For
executing the SPs we are using Execute sql task.
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.
