ETL 中 DataflowTask 内的循环控制
作为 SSIS 和 ETL 过程的新手，我想知道是否有办法循环遍历 DataFlowTask 中的记录集并将每一行（从行派生参数）传递到存储过程（ETL 阶段的下一步） 。一旦我将行传递到存储过程中，我希望将每次迭代的结果写入表中。 有谁知道该怎么做？
谢谢。
Being fairly new to SSIS and the ETL process, I was wondering if there is anyway to loop though a record set within a DataFlowTask and pass each row (deriving parameters from the row) into a Stored Procedure (the next step in the ETL phase). Once i have passed the row into the stored procedure, I want the results from each iteration to be written to a Table.
Does anyone know how to do this?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SSIS 数据流中的任何 OLEDB 命令转换（用于执行数据库命令）都会针对每个输入行执行一次 - 我认为这是您想要的行为。更多详细信息请点击此处。
在您的场景中，您需要的最低限度是：
请注意，这不会提供出色的性能 - 最好尝试重构您的存储过程以一次性对整个输入集进行操作（在这种情况下，您可以会在控制流中使用执行 SQL 任务）。
Any OLEDB command transformation (which is used to execute a database command) in an SSIS dataflow is executed once per input row - which I think is the behaviour you want. More details here.
In your scenario, the minimum you would need would be:
Note that this isn't going to give great performance - it might be better to try and refactor your stored procedure to operate on the whole input set in one go (in which case you'd use an Execute SQL task in the control flow).
以下结构可以使用：
创建一个对象变量。 (recordset_object)
创建字符串变量。 (record_string)
在控制流中创建“执行 SQL 命令”。该命令应返回您想要循环的记录集。
在“执行 SQL 命令”中，在常规选项卡中设置结果集 = 完整结果集。
在“执行 SQL 命令”中，
在“执行 SQL 命令”中，在结果集选项卡中设置结果名称 = 0 和变量名称 = (recordset_object)。
创建“Foreach 循环容器”，并在“执行 SQL 命令”和“Foreach 循环容器”之间创建优先约束。
在“Foreach 循环容器”的“集合”选项卡中，设置 Enumerator = Foreach ADO Enumerator。
在“
在“Foreach 循环容器”中，在“集合”选项卡中设置 ADO 对象源变量 = User::recordset_object。
在“Foreach 循环容器”的“集合”选项卡中，设置枚举模式 = 第一个表中的行。
在“Foreach 循环
在“Foreach 循环容器”的“变量映射”选项卡中，设置变量 = User::record_string 且索引 = 0。
在控制流设计图面的“Foreach 循环容器”中，添加“执行 SQL 命令”。
对于子“执行 SQL 命令”，您可以 (13) 将 SQLStatement 设置为使用生成要执行的代码的变量，或 (14) 映射到参数，或 (15) 创建 record_string由代码执行的 SQL 命令。
如果您使用变量，则它可能类似于 User::sql_code_string ，其值可能类似于“EXEC schema.some_stored_procedure '” + @[record_string] +“';”。然后，您可以在子项“执行 SQL 命令”= 变量的常规选项卡中设置 SQLSourceType，并将 SQLStatement 设置为 User::sql_code_string。
如果使用参数，则在参数映射的子“执行 SQL 命令”中
选项卡设置变量名称 = User::record_string、方向 = 输入、数据类型 = VARCHAR、参数名称 = 0、参数大小 = -1。在子“执行 SQL 命令”的“常规”选项卡中，将 SQLStatement 设置为“EXEC schema.some_stored_procedure ?”。
与 13 类似，但您可以执行 User::record_string，而不是创建单独的变量。如果您的数据集返回的 record_string 的内容是您要执行的查询，则此方法可行。
与 @Ed 的解决方案相比，我通常更喜欢这种方法，您可以为每条记录添加额外的步骤。例如，我经常在控制流中添加其他对象，例如脚本任务、数据流和执行 SQL 命令。从我的角度来看，这是一种更灵活、更容易理解的方法，但 @Ed 的解决方案绝对符合您问题的标准。
祝您好运，如果您需要有关说明的说明，请告诉我。
The following structure would work:
Create an object variable. (recordset_object)
Create an string variable. (record_string)
Create an "Execute SQL Command" in the control flow. The command should return the record set that you want to loop through.
In the "Execute SQL Command", in the General tab set the Result Set = Full result set.
In the "Execute SQL Command", in the Result Set tab set the Result Name = 0 and Variable Name = (recordset_object).
Create a "Foreach Loop Container" and create a precedence constraint between the "Execute SQL Command" and the "Foreach Loop Container".
In the "Foreach Loop Container", in the Collection tab set Enumerator = Foreach ADO Enumerator.
In the "Foreach Loop Container", in the Collection tab set the ADO object source variable = User::recordset_object.
In the "Foreach Loop Container", in the Collection tab set the Enumeration mode = Rows in the first table.
In the "Foreach Loop Container", in the Variable Mappings tab set teh Variable = User::record_string and the Index = 0.
In the "Foreach Loop Container" in the design surface of the Control Flow, add an "Execute SQL Command".
For the child "Execute SQL Command", you can (13) set the SQLStatement to either use a variable that generates the code you want to execute, or (14) map in a parameter, or (15) make the record_string a SQL command that is executed by the code.
If you use a variable, then it could be something like User::sql_code_string and its value could be something like "EXEC schema.some_stored_procedure '" + @[record_string] + "';". You would then set the SQLSourceType in the General tab of the child "Execute SQL Command" = Variable and set the SQLStatement to User::sql_code_string.
If you use a parameter, in the child "Execute SQL Command" in the Parameter Mapping
tab set Variable Name = User::record_string, Direction = Input, Data Type = VARCHAR, Parameter Name = 0, Parameter Size = -1. In the General tab of the child "Execute SQL Command", set the SQLStatement to "EXEC schema.some_stored_procedure ?".
Similar to 13, but instead of creating a separate variable, you can execute User::record_string. This could work if the content of record_string that was returned by your data set is the query you want to execute.
I generally prefer this approach over @Ed's solution you can include additional steps for each record. For instance, I often add in additional objects in my Control Flow like Script Tasks, Data Flows, and Execute SQL Commands. It's a more flexible, easy to understand approach from my perspective, but @Ed's solution definately meets the criteria of your question.
Good luck and let me know if you need clarification on the instructions.