The Parallel Loop Task is an SSIS Control Flow task, which can execute multiple iterations of the standard Foreach Loop Container concurrently. Based on actual tests, when a CPU-intensive process is run on a 4-core machine using this Parallel Loop Task, it executes 3 times faster as compared to running the same process sequentially.
Use the General page of the Parallel Loop Task Editor dialog to configure the parameters needed to execute a loop in parallel.
Specify the task name.
Specify the task description.
Specify whether or not the Parallel Loop Task terminates if parallel execution fails.
Specify the package connection.
Type the Foreach Loop you want executed concurrently, or click the browse button () and locate it. You must disable the target loop because the loop execution will be done by the Parallel Loop Task.
Specify the number of threads to use for concurrent execution. A value of -1 allows the maximum number of concurrently running iterations to equal the number of processors plus two. The specified value is constrained to be equal to or less than the Packages' MaxConcurrentExecutables property.
Considerations about Foreach Loop iteration variable
It is mandatory to use an iteration variable scoped inside the loop container. Failure to do this will result in errors and/or concurrency issues.
How to set up a dynamic loop connection
Follow the steps below to set up a dynamic iteration-variable dependent connection:
- Create a variable with the package scope and use this variable in the connection manager to construct your expression.
- Create a new variable with the same name and type, but with the loop scope.
The Parallel Loop Task will use the variable with the loop scope to evaluate the connection manager expression and build the dynamic connection.