Foreach Loop Container : assigning variable
-
Dear all,
I have this table :
PK AnimalID Name
1 1 Bird
2 1 Cat
3 1 Dog
4 2 Horse
5 2 Tiger
6 2 Bear
7 3 Fish
I want to generate an excel file for every AnimalID, so with above table, i will have 3 excel file :
Excel file 1 would be like this :
AnimalID Name
1 Bird
1 Cat
1 Dog
Excel File 2 :
AnimalID Name
2 Horse
2 Tiger
2 Bear
Excel File 3 :
AnimalID Name
3 Fish
I want to generate it through SSIS. My idea is like this :
The first Data Flow insert those AnimalID (select distinct AnimalID ...) into a Recordset Destination, which stores the value of AnimalID in a variable called UserTable. Variable UserTable has "Object" data type.
Then, I use Foreach Loop Container, with this configurations:
Enumerator = Foreach ADO Enumerator
ADO Object Source Variable : User::UserTable (that variable i created before)
Enumeration mode : Rows in the first table
When I execute this package, it will loop 3 times.
The question is : How do I catch those AnimalID in the Foreach Loop Container and assigned it in a variable? So, i can use that variable as a parameter in OleDb Source inside the Foreach Loop Container.
Or, is there any other ideas which more simple than mine 😀 to handle this kind of situation?
Thanxs 😀
-
Problem solved.
Variable mapping do it function very well :p
Sry for asking in a hurry..
Now, i have to figure out how to generate excel file for each AnimalID.
And, i still want to know is there any simple idea than mine to generate those excel?
Thanx.
-
Create a blank Excel 'template' file - contains just the field names.
0) Copy this file to, say, ID.xls and create a connection in SSIS to ID.xls.
Your dataflow task in your foreach loop then does the following:
1) Copy from template file to ID.xls
2) INSERT IDdata to ID.xls
3) Copy ID.xls to 1.xls (or 2.xls, or whatever ID is currently being processed)
Step (0) is required so that you can create the connection and meta data properly in the first place.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply