Friday, October 17, 2008

SSIS Multithreading (Parallel execution in SSIS)

I have always been curious how I could control the parallelism of different flows in Integration Services Packages. But I was not able to find about it earlier. Microsoft gives us enough ability to execute our packages concurrently. There are a few properties of package (Just right click control flow designer and select Properties).

MaxConcurrentExecutables:This specifies the maximum number of execution flows allowed in parallel at any point in the package. It specifies the maximum number of threads that a package can create for concurrent execution. Remember that we can not specify 0 (Zero) for this property because this would mean zero thread meaning no execution flow.

The default value is -1. This means 2 more than number of processors.

You might be wondering about scenarios where the number of parallel execution flows might be greater than the number of threads specified. In all such cases, the remaining threads would have to wait until the time any thread completes execution.

EngineThreads:This property is used to incorporate parallelism into data flow tasks. It has a default value 10. Be careful in specify the value. This is because this property, along with MaxConcurrentExecutables, decides the number of parallel execution flows in a package simultaneously.

Additionally two properties of Dataflow tasks can be used to specify Temporary storage to any faster disks. These properties are BLOBTempStoragePath and BufferTemppStoragePath.