Using Multiple variables using for loop in SSIS

Question

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.

What is the point of looping through like this? Why do you think you need to do it? Just select all of the records in one go and insert them into the target table. If this is not a suitable solution, please edit your question and clarify why you are doing this — Nick.Mc
– Nick.Mc, Commented Jul 12, 2017 at 2:46
ok going to edit the question. The reason I have to loop through them is because the I am querying against very big database which are divided by area/subarea and I have to loop through each area/subarea to get the data and cannot do this using IN clause as it would take forever to run. Sorry if I was not clear. — aquacoder
– aquacoder, Commented Jul 12, 2017 at 13:58
Do you want to copy all records or a subset of records? Are the source and target databases on the same SQL Server? (I assume SQL Server - please clarify). How many records? I would be suprised if loading them piece by piece over SSIS is any faster than just one load in one big go. Anyway, do you have an existing table that lists all areas and subareas? What have you built so far? — Nick.Mc
– Nick.Mc, Commented Jul 12, 2017 at 14:05
The source is a oracle OLEDB database and I am trying to bring the data over to SQL Server using SSIS. I have a very big query and I want to get all the records based on each area/subarea to sql server. Are you suggesting creating a temp table which lists all the area and subarea in oracle database and looping through each one by one. Still it would need the use of for loop right? — aquacoder
– aquacoder, Commented Jul 12, 2017 at 14:29
After all this I still don't know if you want to copy all the data and I still don't understand why you think copying it in chunks is quicker. — Nick.Mc
– Nick.Mc, Commented Jul 12, 2017 at 22:35

VKarthik · Accepted Answer · 2017-07-12 20:54:20Z

1

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

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.
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.

answered Jul 12, 2017 at 20:54

VKarthik

1,4492 gold badges17 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aquacoder Over a year ago

Thank you so much for giving me the idea but I did some more work and used For loop instead and it is working great for me.

aquacoder · Accepted Answer · 2017-07-14 14:20:25Z

0

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.

answered Jul 14, 2017 at 14:20

aquacoder

11 silver badge4 bronze badges

Collectives™ on Stack Overflow

Using Multiple variables using for loop in SSIS

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related