SSIS顺序处理 [英] SSIS Sequential Processing
问题描述
在同一数据流任务中，我有5个独立的数据流，每个数据流都有一个源和一个目标。如何使它们顺序运行。它们似乎并行运行。我可能会在其他数据流任务中执行此操作。但是我该如何在单个数据流任务中做到这一点
同一任务中没有独立的数据流。我知道导入/导出向导会做到这一点，但是仅仅因为Microsoft的团队在做某事，并不是最佳做法。数据流通过免费并行化获得其功能和性能。如果您不希望这样做，那么为了维护将来的代码，请创建4个其他数据流并将其复制/粘贴到其中。这样做绝对没有害处。
为了真正回答上述问题，您将不得不引入某种依赖。在可怕思想的万神殿中，紧随其后的是以下事物。
我假设您的数据流中包含多个独立的流，看起来像是OLE DB目标的Source（无关紧要）。修改源查询或在其后添加派生列，然后创建一个类型为int（DT_I4）的列，并将其命名为唯一的 HackedSortKey 并为其分配值1。
删除除其中之一以外的所有OLE DB目标。而是将其替换为OLE DB命令。使用OLE DB命令的价值在于它允许行通过。顾名思义，OLE DB目标只是数据的接收器。它的唯一输出列是错误一。分别为您的 INSERT 查询。这就是Command对象的设计难题，但是当它们在数据库上执行单例操作时，您也会遇到它们的运行时难题。 哦，我要插入一行。我发出命令的那一刻。哦，我要插入一行。请一会儿。每一行都会得到这种处理。
将您的第一个Source转换为Command对象。将完全阻止组件附加到它。使用排序。按HackedSortKey列排序，删除重复项，不允许其他列通过。这样做的目的是强制等待。只有所有所有数据通过上面的OLE DB命令传递之后，排序才会释放下游行（因为直到看到所有行，它才知道排序的顺序）。通过选择独特的价值事物，这会将原始行减少为A行。
流A中的Logjam，遇到流B。流B现在看起来像源B ->排序B->合并连接AB-> OLE DB命令B->在HackedSourceKey上排序。需要排序B，因为合并联接需要排序的输入。 将匹配，因为我们的假匹配列中使用了相同的值。但是，您需要确保它是LEFT OUTER JOIN匹配而不是INNER。但是实际上，您想要使用不同的数据流并具有优先级约束来管理执行。
I have 5 independent data flows in the same data flow task each having a source and destination. How can I make them run Sequentially .they seem to run in parallel . I may do it in different data flow tasks. but how can i do it in a single data flow task
Don't have independent data flows in the same task. I know the Import/Export wizard will do that but just because a team at Microsoft does something, doesn't make it a best practice. The Data Flow gets its power and performance through "free" parallelization. If you don't want that, please, for the sake of those who maintain your future code, create 4 additional data flows and copy/paste into them. There is absolutely no harm in doing this.
For the sake of actually answering the above question, you will have to introduce a dependency of some sort. In the pantheon of horrible ideas, the following is near the top.
I assume your data flow with multiple independent flows within it looks something like Source (doesn't matter) to an OLE DB Destination. Modify your source query or add a Derived Column in after it and create an column of type int (DT_I4) and call it something unique HackedSortKey and assign a value of 1 to it.
Remove the existing OLE DB Destination on all but one of them. Replace it with an OLE DB Command instead. The value of using OLE DB Command is that it allows rows to pass through. As the name implies, OLE DB Destination is only a sink for data. The only output column from it is an Error one. Write your INSERT queries for each. That's the design pain of the Command object but you'll also experience the run-time pain of them as they perform singleton operations on the database. "Oh, I have a row to insert. One moment while I issue the command. Oh, I have a row to insert. One moment please." Every single row will get this treatment.
Take your first Source to Command object. Attach a Fully Blocking component to it. Use a Sort. Order by HackedSortKey column, remove dupes and allow no other column through. The point of this is to force a wait. Only once all of the data has passed through the OLE DB Command above will the Sort release downstream rows (because it won't know what the sort isuntil it's seen all rows). By selecting the distinct value thing, this will reduce the original rows to A row.
Logjam in stream A, meet stream B. Stream B now looks like "Source B" -> "Sort B" -> "Merge Join AB" -> "OLE DB Command B" -> "Sort on HackedSourceKey". The "Sort B" is needed because Merge Join requires sorted input. There will be a match as the same value is used in our fake match columns. However, you will need to make sure it's a LEFT OUTER JOIN match and not an INNER.
Lather, rinse, repeat this process for the remaining data flows. But really, you want to use different data flows and have the precedence constraint manage execution.
这篇关于SSIS顺序处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
