Parallel execution in SSIS

Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors.  To support parallel execution of different tasks in a package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads.  If you are like me, you probably did not even know about these two properties, and therefore were unaware of the opportunity to make your SSIS packages execute faster.  A description of each property:

The MaxConcurrentExecutables property is a property of the package.  This property defines how many tasks can run simultaneously by specifying the maximum number of executables that can execute in parallel per package.  The default value is -1, which equates to the number of physical or logical processors plus 2.

The EngineThreads property is a property of each Data Flow task.  This property defines how many threads the data flow engine can create and run in parallel.  The EngineThreads property applies equally to both the source threads that the data flow engine creates for sources and the worker threads that the engine creates for transformations and destinations.  Therefore, setting EngineThreads to 10 means that the engine can create up to ten source threads and up to ten worker threads.  The default is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2.

One other thing to consider: If you are using the Execute Package Task, the child package to be executed can be run in-process or out-of-process by use of the ExecuteOutOfProcess property.  If a child package is executed out-of-process, you will see another dtshost.exe process start.  These processes will remain “live”, using up resources, for quite a while after execution is complete.

If executing in-process, a bug in a task of the child package will cause the master package to fail.  Not so if executing out-of-process.  On 32-bit systems a process is able to consume up to 2GB of virtual memory.  Executing out-of-process means each process can claim its own 2GB portion of virtual memory.  Therefore if you are simply using many packages to structure your solution in a more modular fashion, executing in-process is probably the way to go because you don’t have the overhead of launching more processes.

More info:

SSIS – An Inside View Part 4

SSIS Nugget: Engine Threads

Improving the Performance of the Data Flow

Designing Your SSIS Packages for Parallelism (SQL Server Video)

Too Many Sources in a Data Flow

SSIS Operational and Tuning Guide

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication, SSIS. Bookmark the permalink.

3 Responses to Parallel execution in SSIS

  1. Anuj Chadha says:

    I have a parent package running mutiple children packages (about 20). The MaxConcurrentExecutables is set to -1, Children processes Execute in process.
    SQL Server 2008 R2, 64 bit server. When I run the parent package I get the following ERROR Error: Error 0xC0011008 while preparing to load the package. Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.
    When I set the MaxConcurrentExecutables to 1 or 2 the jobs runs fine, but beyond that it fails on arbitary children packages.

    Could you point out what mistake I might be doing? I need to gain performance by running the packages in parallel.

  2. James says:

    “the child package to be executed can be run in-process or out-of-process by use of the ExecuteOutOfProcess property”

    unfortunately I don’t think setting ExecuteOutOfProcess to true makes it run asynchronously. It’s a bit of a misleading property. You have to use an ExecuteProcess task to accomplish true Parallel execution.

  3. vishal kaushik says:

    Hello James,
    I have a package, where in db table, it has 10 rows. I want to achive parallel processing of each row with the help of sepearte tasks. I am also not sure how many parallel task i need to add to package to achive this. Is it possible? if yes, pls guide.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>