CODEWITHSUNDEEP
loopsvariablesfor-loopssiscontainers
aquacoder
aquacoder

Reputation: 1

Using Multiple variables using for loop in SSIS

I am very new to SSIS and below is what I am trying to do. I have a huge OLEDB database as source and the tables which I want to access is divided by multiple areas. For example:

Select * from employee where area='01' and sub_area='02'

What I want to do is use the same query to loop through all the departments and sub departments and insert the data in the SQL server table using SSIS. What I have tried so far is creating multiple variables for each department and sub department, then I was trying to use FOR loop container to loop through all the variables. I am trying to loop through because the database is huge and dividing it by area/subarea gives faster results rather than querying the whole database which takes forever to execute. I went through quite a few examples of for loop container but I am still not sure how do I loop through and let the program know to go to the next set of variables (Dept and sub dept in my case) after finishing the first set. Any help will be appreciated. Thank you so much.

Upvotes: 0

Views: 4178

Answers (2)

aquacoder
aquacoder

Reputation: 1

Here is what worked for me. I created variables named counter and counterlimit. I set counter to 0 and counterlimit to 4. Then in the for loop container- Initexpression was set to @Counter=0, Evalexpression was set to @Counter<@CounterLimit and assign expression was set to @Counter=@Counter+1. Then in the where condition I used Case statement with @Counter variable to say that if @Counter=0 then fetch area=1 and subarea=2 and so on.

Here area and sub area are also variables. This way I used for container to loop through all the area and subarea to get the results. Not sure if it the best way but it solves my issue.

Upvotes: 0

VKarthik
VKarthik

Reputation: 1429

Looks like you want to bring your data in chunks using area and sub area id's. Here is what you would need to do. Declare the following variables -

VariableName,Type
objAreaSubAreaList,Object
sArea,String
sSub_Area,String
sSQL_GetEmployeeDataByAreaSubArea,String

For the last variable in the expression window, have this expression in it -

"select * from dbo.Employee where Area = '" + @[User::@sAreaId] + "' and Sub_Area = '" + @[User::@sSub_Are] + "'"

Steps to do - 1. Drag a Execute SQL Task with your 'SourceDB' connection. Set the following properties - General Tab - SQLStatement - select Area, Sub_Area from dbo.Employee group by Area, Sub_Area ResultSet - Full Result Set Result Set Tab - Click on 'Add' and assign it the variable - objAreaSubAreaListand give 'ResultSetName' as 0

  1. Drag a 'Foreach Loop Container' and connect the above task to this task. Collection Enumerator - Foreach ADO Enumerator Set the variable as - objAreaSubAreaList Variable Mappings Add the two variables sArea, sSub_Area in that order.
  2. Within Foreach loop drag a 'Data Flow Task'. Open the Data Flow Task. Drag and drop OLE DB Source task and set the connection. Set the Data Access Mode as 'SQL Command from Variable'. Set the Variable as sSQL_GetEmployeeDataByAreaSubArea and go ahead with the rest of the tasks.

Upvotes: 1

Related Questions