[SSIS] Parallel ForEach Loop - by Jamie Thomson

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 544304 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 3/24/2010 10:15:35 AM
Access Restriction Public


The ForEach loop is a very very useful tool for SSIS developers but it does have the advantage that it works serially i.e. Each iteration of the ForEach executes one after the other.

In the future multicore processors will be ubiquitous so would it not make sense to give us the ability to execute some of those iterations concurrently?

Here's a use case where this would be useful:
We are looping over a list of computers from which we need to fetch some files via FTP. Why can we not hit each of those remote computers at the same time? There is no reason why not yet the ForEach Loop in SSIS2008 prevents us from doing so.
Sign in to post a comment.
Posted by Koen Verbeeck on 4/24/2014 at 11:06 PM
I cannot understand why this feature hasn't been implemented yet.
If you want to have some sort of parallellism, you'd have to copy paste tasks around, which only increases complexity and maintenance.
Furthermore, you don't know always upfront how many cpus you're going to have in production. It would be great if there was just a setting "utilize maximum number of threads possible".
Posted by Miron contrib on 2/12/2014 at 5:42 PM
Need to throttle calls into services.
But only individual calls.
Having ability to batch execution of cycles, say, fetch ( up to ) 4 cycles launching inner process of "for each", wait for all, proceed to next batch would be an very welcome optimization.
Posted by Ally_R on 1/17/2014 at 7:08 AM
+1 for me. I need to copy files from a number of remote servers. I want to use robocopy called from exec process task, in a loop with an object full of server names. The server names need to be dynamic
Posted by SAinCA on 6/14/2013 at 9:47 AM
This, I agree Robert, IS a very important feature-add. Thankfully my current machine has only 12 logical cpus, so, yes, I have a container in which there are 12 near-identical containers each looping through their set of "directives" determined by their ID number. Dev boxes have fewer cpus, so the package uses a simple concurrency limiter of n-2 for > 4 or 3 for a 4-way logical machine, so the package simply runs "empty directive" loops momentarily.

I can see HUGE savings in reduced complexity and repetitive task maintenance elimination, let alone not having to wrestle with the (awful at moving and sizing) designers for 16 objects (in case we went to an 8-core, which we didn't, and I hate the designers so much I won't change what's working)!

Gets my +1 without a doubt.
Posted by Robert Heinig II on 3/11/2011 at 4:25 AM
Automatic parallelism is a major field of study. Compared with what compilers or the SQL optimizer do, SSIS has it easy - parallelism is mostly designer-controlled. Such a thing as a Loop container with a MAXDOP (choose better name) property would be frightfully trivial to implement in the context if SSIS's execution model, at least it seems so to me.

Thus - IMHO this is a VERY good suggestion: Large potential gains for very little implementation work and zero impact on existing designs. Why the item is marked closed as not solvable is a mystery to me.
Posted by Microsoft on 8/10/2010 at 4:50 PM
Thank you for contacting us. After analyzing this issue and prioritizing it in the context of our current development plans, we have found regretfully that it does not meet the bar to be included in an upcoming product release. If you feel that you could provide us with more data to change that judgment, we welcome any further information. Your feedback continues to be invaluable to us and we take all your communication seriously. Please continue to contact us with any questions, suggestions and issues as they arise.

     The SSIS Team
Posted by COZYROC on 7/26/2010 at 8:19 AM
If you can use third-party solutions, check the commercial CozyRoc Parallel Loop Task. The documentation is available here:


It accomplishes the requirement identified and described by Jamie. Cheers!
Posted by F_clef on 3/26/2010 at 9:29 AM
Never mind, I figured it out.
Posted by F_clef on 3/26/2010 at 9:28 AM
I see your point about adding multiple tasks. Is there a way to change your vote on here?
Posted by Jamie Thomson on 3/26/2010 at 2:07 AM
Hi Wan,
Thanks for the comment.

"Isn't that a possible solution?"
Yes that's a possible solution (I'd call it a round-robin technique), but its not as easy to build as what I proposed.
Plus you're still only going to be executing as many concurrent tasks as your package contains, the advantage of what I suggested is that SSIS can be in control of the number of concurrent threads. If you're on a 64-way are you going to build 64 ForEach loops into your package? I doubt it.

"how would you configure this new Parallel ForEach Loop?"
Not sure I understand the question, sorry.

"I guess you can call it a cylinder."
Call it a loop. Call it a cylinder. Call it anything you like. I don't really care what you call it.

Posted by F_clef on 3/25/2010 at 3:52 AM
Hi Jamie

I'm not sure I understand your rationale behind this enhancement. In the use-case, why not assign each remote computer to a series of numbers and have multiple ForEach Loop containers that are responsible for processing the work from its pre-assigned group of computers? Isn't that a possible solution?

I voted this down since it adds customizable features to an "off-the-shelf" product that with some minor tweaks can work as you requested. Besides, how would you configure this new Parallel ForEach Loop? That's not even a loop anymore. I guess you can call it a cylinder.
Posted by Jamie Thomson on 3/24/2010 at 10:22 AM
Please post comments and use cases if you vote this up. if you vote down please say why.