How to run parallely/sequentially

I am having different packages under one project. Each package is having one one control flow, which internally contains one data flow. I want to run these data/control flows in one sequence. I am not able to drag all of these data/control flows into one sequence container. Could anyone tell how to do this in SSIS?

I would create a controller a package, and use Execute Package Tasks to call the existing worker packages. Do not constrain the Execute Package tasks and they will execute in parallel.

|||I didn't get it.

My question is how to run different packages sequentially or parallely. e.g I am loading customer dimension in one data flow(one package). Then I am loading parts dimension using one more package. Then I am loading Calendar Dimension using another package. How do I combine these in a single process flow/workflow, so that one flow can run after/with another .|||You haven't given any details of the workflow you want, so I'll just guess. If I have two dimension load packages and one fact load package I would load my dimensions in parallel, but would want my facts to load after all dimensions for example.

So I would create a new package, the controller package, and add an Execute Package Task to call my Dim 1 package. Then add a second Execute Package Task for my Dim 2 package. Then add a third Execute package task for my Fact package. Now drag and drop the workflow (green dangling lines) from the Dim 1 Exec Pkg Task to Fact Exec Pkg task. Repeat for Dim 2 to Fact.

So now the dimensions are unconstrained and will execute in parallel, but the fact will wait for both dimensions to complete successfully.
Dim1Fact
Dim2||||Thanks DarrenSQLIS, I got it|||The execute package task is working. but when I run my sequence container, which contains 3-4 execute package tasks--it opens up each and every pacakge on my screen (while running). what do I need to do, if I don't want to open them. I want to execute this sequence, without opening up each and every package..|||That's just what the debugger does. If you run it with dtexec (Ctrl-F5) that won't happen. But then you won't get them purty colors. sql

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html