Parallel Processing in SSIS Part 2

In the previous post I have covered the MaxConcurrentExecutables property in SSIS Parallel processing. In this post, which is continuation to the previous post,  I will cover EngineThreads property

Where can I set the EngineThreads property?

EngineThreads property is available with each dataflow task. As per MSDN BOL it is “An integer that specifies the number of threads that the data flow task can use during execution” which actually means number of threads that can run parallel in dataflow pipeline. To set the value for this property right click on the dataflow task and select properties, in the Misc section you will find EngineThreads.

In the above screen shot you see the value of 5 which means 5 Engine threads can run in parallel.

Note: EngineThreads governs both Source threads and Worker threads.

What is difference between source threads and worker threads?

Source threads works on source components in the data flow and the worker threads works on transformations and destinations.

If an Engine thread governs both Source threads and Worker threads what does EngineThreads value 2 implies?

A value of 2 for engine thread implies that up to 2 source threads and up to 2 worker threads.

In the above screen shot we can observe that records are being pulled from the source (source table has 1 million+ records) [by source thread] and at the same time we can observe that rows and passed out of look-up transformation [by worker thread]. This clearly depicts that for a value of 2 EngineThreads has resulted in 2 source and 2 worker threads that are running in parallel.

If my EngineThreads property is set for 2 and there are dependent paths in the data flow how does this work?

As explained EngineThreads property will be 100% utilized provided there are independent paths. If there are dependent paths EngineThreads will be used where ever possible. In the below screen shot we can see that there is one source and multiple transformations and destinations. Since there is only one possible source only one source thread was created and 2 worker threads are created 

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