Parallel Processing in SSIS Part 1

Hi, This is Bala Krishna Ragala and I am an integration expert in MS technologies having work experience in SSIS and BizTalk. I am starting this blog to share knowledge on SSIS and BizTalk areas for now (  I have plans to extend this for other areas of MSBI i.e., SSAS and SSRS ). 

Please share your valuable feedback in the form of comments or you can write me on baluragala@gmail.com

To start with I will be covering the parallel processing concepts in SSIS. 

In this post we will understand about the parallel processing in SSIS in the Question & Answer pattern.

Does SSIS support parallel processing?

Yes. SSIS supports parallel processing and there are 2 different properties, MaxConcurrentExecutables and EngineThreads, which control this.

Where can I set the MaxConcurrentExecutables property?

This property can be set in control flow at the package level. Right click on empty area of the control flow and select properties and you can find this property in the Execution section of properties window. The default value of this property is -1, which equals to “Number of processors + 2”. If package has 10 independent executables in control flow and your computer has 4 cores then 6 executables will run in parallel at any point of time during package execution.

  

Though MaxConcurrentExecutables is set for default value of -1 and my computer has 2 processors but why I do not see tasks running in parallel?

The MaxConcurrentExecutables will have effect provided there are independent tasks that can run in isolation. In the below case every executable has a linked precedence constraint and they need to obey the logic defined, which created the dependency among the tasks. In simple terms if the workflow has sequential precedence constraints then this property will not have any effect.

What are the values that can be set for MaxConcurrentExecutables property?

Any value greater than 0 or -1

What is the highest value that can be set for MaxConcurrentExecutables property?

On a 32-bit machine (my machine is 32-bit) it defaults to 128 though the value is set higher than 128. I did not get a chance to explore 64-bit but I presume this would be the same. Please correct me if I am wrong.

  

What happens when I try to set the invalid value for MaxConcurrentExecutables property?

You will get a design time warning.

Can I set the MaxConcurrentExecutables property programmatically?

Using SSIS Object model you can this property programmatically and this property is available in Package object.

Package pkg;

pkg.MaxConcurrentExecutables = 4;

In the next post I have covered about EngineThreads Property