Executing Stored Procedure & Insert Statement, Objects, Foreach Loop Container in SSIS

'Execute SQL Task' component is not only restricted to execute simple select queries, it can also execute stored procedures, insert and update statements as well.

In this post I would like to discuss following functionalities of SSIS :
       * Executing stored procedure using 'Execute SQL Task'
       * Full result set and Object variable

       * For each ADO Enumerator on 'For each loop container'

Executing stored procedure using 'Execute SQL Task' :

For implementing this demo I am using two tables, they are user and BIUsers. The structure and sample data of these tables are given below

CREATE TABLE [dbo].[User](

[UserID] [int],

[UserName] [nvarchar](50),

[DeptName] [nvarchar](50)

)

insert into [User] values(1,'murty','BI')

insert into [User] values(2,'dileep','BI')

insert into [User] values(3,'hareesh','IP')

insert into [User] values(4,'akash','IP')

insert into [User] values(5,'raghu','BI')

CREATE TABLE [dbo].[BIUsers](

[userid] [int],

[username] [nvarchar](50)

)

Table User consists all the user names along with their department names, here I am using one stored procedure to get all the users who are working in one particular department. For this Stored Procedure I need to pass deparment name as a parameter so that it will give all the users who are working under this department. Below is the defination of stored procedure :

create procedure [dbo].[UsersDept]

@dept nvarchar(50)

As

Begin

select UserID,UserName from [user] where DeptName=@dept

end

Once we created above two tables and stored procedure, now we need to execute the stored procedure using 'Execure SQL Task'. Before going to implementing this we have to create two package level variables, one is for storing department name and the other one is storing result set data. Below are the variables created at package level.

Set the connection properties for 'Execute SQL Task', sample figure is given below.

Full resultset and Object Variable

Here we need to set the result set property, it contains four different values among these four options we need to take 'Full Result Set', why because we are retriving all the users details corresponding to one particular department. Connection Type is OLEDB and Sql statement is as follows :

EXEC UsersDept ?

The symbol '?' (question mark) indicates it is accepting parameter, so we need to set parameter in the parameter mapping tab. Here we are using variable dept as input parameter, here the variable dept already it contains a value 'BI'. The below figure explains you how to set the parameter mapping.

On successful execution of the above stored procedure it will return one dataset (users data of one particular department), we need to set result set for catching this dataset. Here we are using our second variable result of type object.

For each ADO Enumerator on 'For each loop container'

Upto now we are storing data into object variable result, now we need to retrieve each and every record and store it in another table BIUsers. For taking every record we are using For Each Loop Container, and our data is lies in Object variable so we need to use For Each ADO Enumerator in the for each loop.

Here we are expecting two columns (userid, username) from object variable.

so we need to create two more variables for holding userid and username information. Here we created userid of type int and username of type string.

In the For each loop container under variable mapping section map these two variables as shown below.,

Finally we need to exeucte the insert statement by taking userid and username as parameters, check the below figure for how to set the properties in execute sql task for executing insert statement.

check the below figure for how to set the parameters for insert statement.