ssis simultaneous execution of query

In advance I'm new to SSIS.

I run a query that gives me distinct values in columnA from TableA that needs to be processed in order (1 then 2 then 3 and so on but the numbers constantly change start and end values).

these columnA values then have groups of values in columnB, and these values have to be run through a stored procedure but they can all run simultaneously. Currently they run in a linear manner

Here is a visual of what I need to do (sudo code)

foreach { foreach { processX } }

what I want: foreach { processA processB ProcessC simultaneously there are no 开发者_如何学Pythoncollisions to worry about }

I am using a control flow in SSIS and it has the foreach loop which is good however I don't know what to use to allow it to run the second part simultaneously.

When I want parallel execution in SQL Server in the Control Flow, I usually put several For-Each loops and bring back a distinct recordset for each one of them.

There currently is no way to run a Foreach Loop in "parallel mode".

The best that I can think of is to rework your architecture into a flexible 'worker' threading model, where you can parallelize independently.

What that would require is two SSIS packages. One to supply the work units, and one to work on them. So the "controller" package would perform the foreach loop on TableA, collecting whatever values it needs to. It would then insert those values into a "work to do" table. The "worker" package would consist of a For Loop, inside of which you'd have an Execute SQL Task that queried the "work to do" table for the first row that wasn't being worked on, and if it found such a row, mark it as being worked on (all inside a transaction to ensure no collisions). You'd then have your "work unit" to do work with, or not. A precedence constraint to your next task would only execute if it actually had some instructions. Your For Loop's Eval expression could be crafted to stop when you don't see any new work units (although you might want a delay in there to make sure your workers weren't faster than the controller).

To run all this, you'd start the controller (in an Agent Job), then start multiple workers (same package, different jobs) - as many as you wanted.