Hello all,
Is there documentation somewhere about multiple execution paths in SSIS control flow? I didn't find documentation anywhere. I have a situation where I have two tasks that take considerable time, but could be executed in parallel (to speed up things) and I was wondering whether SSIS supports parallelism.
To illustrate the issues in simultaneous execution, I created a test SSIS package. In the package, I have five tasks, let's call them T1, T2, T3, T4 and T5. The taks are connected with "green arrows" like this:
T1->T2
T1->T3
T2->T4
T3->T4
T5 is not connected. The tasks can be e.g. Send Mail tasks, that's not relevant to this issue. I put a breakpoint in each task and execute the package.
When I execute package, T1 and T5 become active, i.e. the arrow that displays where the package execution currently is, is in two tasks simultaneously. Now F10 (step over) doesn't seem to work "Unable to step. Not implemented". If I press F5 nothing happens. After I press F5 for a second time tasks T1 and T5 and executed. Why don't they execute with the first pressing of F5? I would additionally like to know whether these two tasks are executed in parallel or sequentially, i.e. in the same thread or in two threads? Is there documentation of this?
The execution stops at T2&T3. Again, pressing F5 doesn't do anything, but the second time I press F5 T2 and T3 are executed.
T1 and T5 will execute in parallel, as will T2 and T3 since they both only depend on T1.Using Execute SQL tasks with WAITFOR DELAY '00:00:05' commands (varying the timeout of each one) is helpful for testing parallelism and precedence.
See the Package.MaxConcurrentExecutables property documentation for how to control the degree of parallelism.
I don't know about your breakpoint question. I don't use them.
|||I think Jay is partially right. SSIS will *try* to run those tasks in parallel. I believe MaxConcurrentExecutables is a hint. There are no guarantees that the tasks will truly run in parallel. MaxConcurrentExecutables will ensure that three tasks can never run in parallel if it is set to 2, for instance. I don't know that setting MaxConcurrentExecutables to 3 will force three available tasks to run in parallel though. (again, for instance)|||
Phil Brammer wrote:
I think Jay is partially right. SSIS will *try* to run those tasks in parallel. I believe MaxConcurrentExecutables is a hint. There are no guarantees that the tasks will truly run in parallel. MaxConcurrentExecutables will ensure that three tasks can never run in parallel if it is set to 2, for instance. I don't know that setting MaxConcurrentExecutables to 3 will force three available tasks to run in parallel though. (again, for instance)
I think that is true for the EngineThreads in a Data Flow, but I've never seen a control flow decide to execute fewer tasks in parallel than allowed with MaxConcurrentExecutables.
http://sqljunkies.com/WebLog/ashvinis/archive/2005/03/29/9825.aspx
|||
Thank you all for your answers.
As a conclusion
SSIS packages support concurrent execution in Control Flow. The exact behaviour of concurrent execution is poorly (if not at all) documented in MSDN, for example if you have a look at the Control Flow documentation there's no mention of such feature (http://msdn2.microsoft.com/en-us/library/ms140234.aspx) The number of concurrent executing tasks is controlled by SSIS package property MaxConcurrentExecutables.
No comments:
Post a Comment