February 3, 2010 at 1:57 pm
I have a table named My_Table with 3 colums.
TableName TextFileDestn ID.
tbl1 C:\file1.txt 1
tbl2 C:\file2.txt 1
tbl3 C:\file3.txt 2
tbl4 C:\file4.txt 2
tbl5 C:\file5.txt 3
tbl6 C:\file6.txt 3
I wanna right a SSIS package where for a particular ID entry in my table, I want that particular table contents to be exported to the destination file specified.
I want to load this table into a dataset and then iterate through the ID column using a variable and then export to the table content to a textfile using a foreach loop container in SSIS.
when ID =1, Table1 contents should be output to textfile1
and then Table2 contents should be output to file2, sequentially.
Since the IDs are 1,2,3.. Can somebody please tell me how I can run 3 sub packages, where I can say if ID in table is 1, then export those corresponding tables to the corresponding files.
But for a particular ID, the exports have to be sequential but since there are many IDs, I need to run the subpackages for each ID parallely.
I know this needs use of variables, conditional for loop. But I dont know how to use 3 variable for 3 different columns to dynamically export data to text files.
help needed. Thanks a lot in advance.
February 3, 2010 at 2:40 pm
So you have a table that you want to load into a variable and then iterate through using a for-each-loop. You then want to call a sub-package depending on the value of one of the fields in the table that was loaded into the variable.
Where I see your first real problem is the reference to running parallelly. This statement:
But for a particular ID, the exports have to be sequential but since there are many IDs, I need to run the subpackages for each ID parallely.
As I read that you can have the IDs run in parallell but internal to an ID you want it to run in order..
The for-each-loop is a FIFO stack, and only one at a time. If you have a finite number of IDs or you could setup several parallel for-each-loops to handle bands of IDs, but you would need to know those bands ahead of time.
For each for-each-loop you would have 4 variables that would line up to the object that holds the result set to iterate through and the three columns in that result set.
Within my for-each-loops, depending on the complexity of my work, I may put a sequence container for each ID, but you are calling sub packages so I would put an execute SQL task at the top that contains a REALLY simple SQL statement like SELECT 1 and then use the precedence operators and expressions to goto a particular sub-package.
That raises another question, all those tables you listed, do they have the same structure or do they vary? If they vary then you need a dataflow(subpackage) for every single variant, if they are the same you can call a package multiple times.
Digest that for a bit and let me know what you think..
CEWII
February 3, 2010 at 2:57 pm
Thanks for your reply. I have developed a few packages earlier, but I am not able to follow the flow in your answer.
To summarize the whole scenarion:
I want to import My_Table into a dataset and then iterate through the ID column using a variable.
I want to write 3 sub packages.
When we loop through the ID:
(a) Sub-package 1: When ID is 1, I want to export table1 to file 1 and then export table2 to file 2
(b) Sub-package 2: When ID is 2, I want to export table3 to file 3 and then export table4 to file 4
(c) Sub-package 3: When ID is 3, I want to export table5 to file 5 and then export table6 to file 6
i want sub-packages 1,2 and 3 to run parallely.
To answer your question:
(a) The IDs in the last column are pre determined. So I know them before hand
(b) The tables 1 to 6 that I have mentioned (for the sake of example) are different tables with varied number of columns and there are 42 tables. This number 42 is fixed.
Does this help ?
Can you please tell me the flow of events ?
Thanks a lot for your help...
February 3, 2010 at 2:59 pm
Yes it does.. LEt me do a quick POC..
CEWII
February 3, 2010 at 3:01 pm
Do they need to be sub-packages? The reason I ask is that it is more complex to explain due to parent package variables..
CEWII
February 3, 2010 at 3:03 pm
They dont need to be sub-packages.
If they can run-in parallel, based on the condition of what ID it is.. Its more than enough..
Thanks a million!
February 3, 2010 at 3:19 pm
Here is the basis..
This gets you to the point you could call the sub packages. Which have to use some "magic" to get the table and filename..
That magic is package configurations using Parent Package variables.
CEWII
February 3, 2010 at 3:20 pm
Then within the F-E-L container do the work, each of the F-E-L containers will run in parallel.
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply