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 havefive 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. Then some general discussion: I've used to think SSIS packages as computer programs, a package seems to me a method or a function. The package has an entry point, as a method. The execution starts from the entry point (as in a method) and the execution proceedsby following e.g. the precedence constraints and loops, like a method containing if-statements and for-loops. The SSIS packages, however, don't seem to directly map to methods/functions. The SSIS package exection may construct several threads, e.g. if there are two tasks in the package with no precedence constraints and there is implicit synchronization between the threads. So instead of thinking a SSIS package is a method I think it's more like a method that creates threads if necessary (the concurrent execution) and synchronized the results of the methods (the T4 task in my original message). The even more out-of-scope discussion: It would be very nice from Microsoft to document howa SSIS package _REALLY_ is executed, containing information on when threads are started, how they are synchronized, how MaxConcurrentExecutables really works etc. Now hundreds or thousands of people around the world have to guess how the execution works. In open source world where I'm more used to work, I (or some more intelligent guy) could just see the code of the SSIS execution engine to see how it works. In MS world we don't have that option. And since the MS documentation doesn't provide accurate information, I think the only option to get "accurate" information are this kind of discussion groups. "Accurate" in that sence that we all only have empirical evidence on the internal behavious of SSIS packages, nobody (maybe except from people from MS knows) how things really works, because we can't see the source code. JM

I'm cutting my teeth on SQL 2005 and have a similar question. I have some SSIS packages I use to mine data with. On the extraction there are about 40 of them I could execute simultaneously. What is the best method of doing this? Currently I'm creating 40 jobs in SQL Server Agent.

You could create a single package with 40 Execute Package Tasks in it, with no precedence constraints between them. SSIS will execute as many in parallel as it can, based on the MaxConcurrentExecutables property of the package.

I am bit confused with the statement/definition of MaxConcurrentExecutables explained on this blog : http://ssis-2008.blogspot.com/2010/08/parallel-processing.html As per the explaination : The default value is -1, which equates to the number of physical or logical processors plus 2. In the example MaxConcurrentExecutables has its default value set as -1 and the server running the package has 8 processors, all 10 tasks will run in parallel (8 physical processor +2 = 10), so does it mean that we dont have to change the MaxConcurrentExecutables value to 8 ? so that it can run all 10 DFT in parallel ? Also if we want to run 4 pkg in parallel , we have to change the MaxConcurrentExecutables value to 4 ? why ? Can some one explain this property in detail , thanks Anky

Hi Anky, A) Yes, you can configure the property as default, and SSIS tries wake up 10 threads to execute in parallel all of them but the completion depends of system avalaible resources. But the 10 task will begin at once. Remember that only control flow engine have parallel execution properties. B) If the 4 packages that you want to run at once are mastered by another package, yes, the property should be set to 4 or leave it in default value (-1)Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky