SSIS Package design question
Posted on 2011-09-05
Would you please let me know what woud you do if the followings are need to be implemented using SSIS 2008 BIDS:
1. Query a database table A
2. Loop through each A record, and query another database table B with part of A fields
3. Based on selected fields of B, set user variables accordingly.
At the same time, loop through each B record, and query the third table C with part of A and B fields.
4. Concatenate a string with selected fields from Table A, B and C with the variables
5, Write the string to a flat file
please, if possible, illustrate the nodes that you would use. Thanks a lot.
I have tried to use 2 ways, but still not able to make them to work:
1. Data Control with OLEDB Source for A table --> OLEDB Comand for B --> Script Component
with this way, OLEDB Command for B is not able to produce outputs. Therefore, I am not able to set variables in side script based on the selected B records.
2. Using Control flow -->Execute SQL Task for A --> Foreach Loop for resultset of A --> contains another Execute SQL Task for B --> still inside the foreach container, using Script Task
With this way, I can have outputs of precedent nodes however, I am not able to referent any resultsets in side the script.
Hope you understand what my problems are.