Implement Parallel Execution in SSIS

SQL Server Integration Services (SSIS) allows parallel execution in two different ways. These are controlled by two properties as outlined below.


The first one is MaxConcurrentExecutables, a property of the package. It defines how many tasks (executables) can run simultaneously. It defaults to -1 which is translated to the number of processors plus 2. Please note that if your box has hyperthreading turned on, it is the logical processor rather than the physically present processor that is counted.



Suppose we have a package with 3 Data Flow Tasks. Each task has 10 flows in the form of “OLE DB Source -> SQL Server Destination”.

Set MaxConcurrentExecutables to 3, then all 3 Data Flow Tasks will run simultaneously.


Now whether all 10 flows in each individual Data Flow Task get started concurrently is a different story. This is controlled by the second property: EngineThreads.


The EngineThreads is a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5.



Again let’s use the above example.

If we set EngineThreads to 10 on all 3 Data Flow Tasks, then all the 30 flows will start off at once.


One thing we want to be clear about EngineThreads is that it governs both source threads (for source components) and work threads (for transformation and destination components). Source threads and work threads are both engine threads created by the Data Flow’s scheduler. So in the above example, a value of 10 for Engine Threads means up to 10 source threads and 10 work threads.


Multi tasking is a double-edge sword. In SSIS, we don’t affinitize the threads that we create to any of the processors. So if the number of threads exceeds the number of available processors, you might end up hurting throughput due to an excessive amount of context switches. Be cautious!


          Runying Mao

Comments (10)

  1. I remember seeing a very early CTP of Yukon and the new DTS. I was very exceited to see a parallel property

  2. I remember seeing a very early CTP of Yukon and the new DTS. I was very exceited to see a parallel property…

  3. Runying Mao and Len Wyatt did a great study of SSIS performance, and posted some results: Getting Optimal

  4. vekat says:

    i am not able to find maxconcurrentexecutables in SSIS package properties.Can any one help me?



  5. TimShea (MSFT) says:

    Hi Vekat, Select your package in your SSIS project  in Solution Explorer, click on the design workspace then  hit F4 for package properties.  At the top click the "AZ[downarrow] button to sort the properties alphabetically then search visually down to the Ms for MaxConcurrentExecutables.  You may need to widen the column slightly by dragging the hidden center line to see the full property name.



  6. Michael Brönnimann says:

    Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).

    Therefore you may have a look into the approach of SQL Parallel Boost at…/default.aspx

    A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 !

    In case you don't wan't to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks.

  7. Vijay says:


    i got number of extraction feeds (from source to staging), performing simple data extraction into staging database. I would like to run these packages parallel in master package( at least 3 packages at a time).  to execute these child packages, need to use number of execute SQL tasks in sequence container of master package. Instead of using number of execute package tasks, can i use for each loop containers( at least 3, to run 3 child packages at a time) in sequence container. Maxconcurrentexecutables Parameter runs different tasks parallel, but not containers.Could you please suggest me the best possible way to implement parallel execution of SSIS packages

    : is it possible to run multiple containers parallel ??


  8. Michael Brönnimann says:

    A free Community Edition of SQL Parallel Boost can be downloaded at

  9. Any change for SSIS 2012? says:

    With SSIS having enjoyed a slew of improvements, is this technique still "Best Practice" or has the Team made advances that supersede this, please?  I'm at the design stage of a parallel execution data flow requiring 6 SQL tasks in parallel to maximize concurrent use of the same raw data from the SQL Data Buffer  being aggregated across year, month, week and day (with growth for 2 others: Quarter and Half-year perhaps) – a form of data caching for complex query performance optimization.  I'd hate to have to do-over!

    Thanks in advance for any guidance.

  10. Syed says:

    Why cant they increase the parallelization like Processor +4 or +6 instead of Processor +2 .

    then we can increase  the DFT's in SSIS Package.