Call Stored Procedure in execute sql task in SSIS

In this post, we will see how we can use the Stored Procedure in Execute sql server. I have created an SP in it is use insert a record into the table. I am creating a simple SP to insert a record into the table.

Create Procedure P_Insert_Emp AS Begin    insert into emp (E_name,E_mobile,E_add)    values ('BageshKumar','888880XXXX','Greater Noida') End;

Now I am taking execute SQL task and creating the connection manager.

 

Since I am inserting a record using SP so that I have selected ResultSet as null. SQL source type will be the Direct and in SQL statement I am calling SP. Click Ok.

The package is ready to run

Before running this package records in the table.

 

Now I am running this package.

 

Package Executed successfully. See the records in the table.

 

The above example is called SP that which does not have any parameter.

 Call parameterized SP

Above example, we see how we call non parametrized SP. Let’s see how we can call parametrized Sp.

I am altering the above SP.

alter Procedure P_Insert_Emp (@E_name varchar(50),  @E_mobile varchar(10),  @E_add   varchar(10) ) AS Begin    insert into emp (E_name,E_mobile,E_add)    values (@E_name,@E_mobile,@E_add) End

I am doing changes in the existing execute SQL task.

SP expects 3 input parameters. We need to create 3 variables where we pass this parameter.

 

In execute sql task we need to pass these parameters with the ?

 

Here in this SP, I have 3 input parameters.

Go to the Parameter Mapping table and assign the variable in the sequence.

Note: Parameter Name it must be in the sequence order of the? Value for example

Parameter index is starting from the 0

Now click ok.

Now the package is ready to run.

Before running this package record in the table

 

 

 Now I am running this package.

   

The package executed successfully. See the records in the table