SSIS Package MaxConcurrentExecutables


If I run SSIS package with many task running in parallel I assume all tasks can start and run independently. But when I debugged my test package many times I found that package had some kind of limitation which did not allow running all tasks simultaneously (all slides with except of last one).

To fix it I had to modify MaxConcurrentExecutables parameter value (=-1 by default).

According to documentationValid values are one and higher, or -1. Other values are invalid. A value of -1 allows the maximum number of concurrently running executables to equal the number of processors plus two. Setting this property to zero or any other negative value fails with an error code that indicates an invalid argument. This property is used when parallelism exists in the workflow. If the workflow is a series of sequential precedence constraints, then this property has no effect.

Ok, now I understand why I have 4 parallel processes (yellow blocks) at one moment. In my case, it was 2 CPU+2 = 4 allowed executables. When I changed this parameter value from -1 to 50 I was able to run all tasks in parralel (last slide).

Well, it was good to know.

Advertisements

About Andrew Butenko

https://www.mcpvirtualbusinesscard.com/VBCServer/a9939be0-be6f-4249-a775-6665eccff2e4/card https://www.microsoftvirtualacademy.com/Profile.aspx?alias=530492
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to SSIS Package MaxConcurrentExecutables

  1. Hello Sir,
    stuck with a question :
    how can we calculate the maximum value of MaxConcurrentExecutables?
    for example I have 10 or more than 10 package task which i want to Execute parallely, so can i set the value of MaxConcurrentExecutables = number of package Task which i want to run parallely or more than this.
    or we have any dependency/Limitation to set the value of MaxConcurrentExecutables.

    Thanks
    Vijay Labhaniya

    • Hi Vijay,
      As described here and
      if you use MaxConcurrentExecutables = -1, the real number of running tasks in parallel is defined as (#_of_CPU + 2).
      But, you are right, that is not a limit of system, it can allocate more threads for your SSIS process. As a result, you can override MaxConcurrentExecutables value and run more parallel tasks. I use empiric approach.
      Not sure how to calculate real limit of parallelism, but I guess it depends on # of CPU, # of cores per CPU and operational system.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s