SSIS Operations: How to execute stored procedure in sql server using SSIS Execute Sql Task.

For this we have to create one stored procedure in sql server. The below query will create one stored procedure to insert into customer table and it takes three parameters as inputs to insert into customer table. The customer table has three columns pid , name and address.

create procedure insertcustoner(@pid int,

@name varchar(50),@address varchar(50))

begin

insert into custoner values(@pid,@name,@address)

end

Now we can execute this stored procedure using Execute Sql Task in SSIS.

Step1: Declare four variables id, name, address and outres (Full Result will store in this variable of type Object).

Step2: drag and drop Execute Sql Task from control flow. click on Execute sql task and do the following steps.

Configure Result set as ‘Full result set’.
Give connection type as OLEDB
In connection give database name
We configured the SQLSourceType property to direct input.
In SQL Statement write this query: - exec insertcustoner ?,?,?

In this query we are transferring the parameter values fetched from first execute task and to stored procedure input parameters. The EXEC keyword used to execute stored procedure.

Step3 : Click the result set option on left side of the editor and add ‘outres’ variable here. Mention result name as 0.

Step 4: Then drag and drop one foreach container and give the enumerator type as “ForEach ADO Enumerator”. Select “outres” variable in ADO Object Source Variable.

Step 4: Then in variable mapping add what all variable(records) you retrieved in first execute sql task. Here the number of variable we are mapping is exactly match with the number of columns we are fetching in first execute sql task.