ssis同时执行查询
提前说一下，我是 SSIS 的新手。
我运行一个查询，该查询为我提供了 TableA 中的列 A 中的不同值，这些值需要按顺序处理（1 然后 2 然后 3 等等，但数字不断更改开始值和结束值）。
然后，这些列 A 值在列 B 中具有一组值，并且这些值必须通过存储过程运行，但它们可以同时运行。目前它们以线性方式运行
这是我需要为每个执行的操作（sudo 代码）的视觉
效果 { foreach { 进程X } 我想要什么
： foreach { processA processB ProcessC 同时进行，无需担心冲突 我在 SSIS 中使用控制流
，它有 foreach 循环，这很好，但我不知道使用什么来允许它同时运行第二部分。
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 collisions 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当我想在 SQL Server 的控制流中并行执行时，我通常会放置几个 For-Each 循环，并为每个循环返回一个不同的记录集。
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.
目前无法在“并行模式”下运行 Foreach 循环。
我能想到的最好的办法是将您的架构重新设计为灵活的“工作”线程模型，您可以在其中独立并行化。
这需要两个 SSIS 包。一位为工作单位提供服务，一位为工作单位工作。因此，“控制器”包将在 TableA 上执行 foreach 循环，收集它需要的任何值。然后它将这些值插入到“待办事项”表中。 “worker”包将包含一个 For 循环，其中有一个执行 SQL 任务，该任务在“work to do”表中查询未处理的第一行，如果发现这样的行，行，将其标记为正在处理（全部在事务内以确保没有冲突）。然后你可以选择与你的“工作单位”一起工作，也可以不与你的“工作单位”一起工作。仅当下一个任务实际上有一些指令时，才会执行它的优先级约束。您的 For 循环的 Eval 表达式可以设计为在您没有看到任何新工作单元时停止（尽管您可能需要延迟以确保您的工作单元不会比控制器更快）。
要运行所有这些，您需要启动控制器（在代理作业中），然后启动多个工作程序（相同的包，不同的作业） - 您想要多少个就可以。
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.