在SSIS中为表中的每一行执行存储过程 [英] Execute stored procedure for each row in the table in SSIS

问题描述

我想对表 MyTable 中的每一行执行带有某些输入参数的存储过程 MyProc 。每行的列将充当 MyProc 的输入值。

I would like to execute a stored procedure MyProc with certain input parameters for each row in the table MyTable. Column for each row will act like input values for MyProc.

如何在SSIS中完成此操作？上层管理人员告诉我要使用SSIS，在这件事上我别无选择。

How do I accomplish this in SSIS? I have been told by upper management to use SSIS and I don't have any choice in this matter.

所以。我做了以下事情：

So.. I have done the following:

1. 创建程序包


2. 创建执行SQL任务
   
   
   
   
   • 在ExecuteSQLTask内部创建的查询
   
   

• 在Foreach循环容器内创建数据流任务。


• 内部数据流任务我创建了OLE DB源


• 将OLEDBSource连接到派生列


• 在步骤3.3中启用连接中的DataViewer

• Create Data Flow Task inside Foreach loop container.
• Inside Data Flow Task I created OLE DB Source
• Connected OLEDBSource to Derived Column
• Enable DataViewer in connection in step 3.3

看来步骤2.1中的所有行都被调用了步骤3.1中的DataFlowTask

It looks like the DataFlowTask in step 3.1 is being called for all rows in step 2.1

我要在步骤3.1中调用DataFlowTask来为在步骤2.1中按相反的顺序记录在所有记录中。

What I want is to invoke DataFlowTask in step 3.1 to get called for each record in the row in step 2.1 AS OPPOSED TO all the records.

一旦完成，我打算在其中的 ExecuteSQLTask ForEachLoopContainer 。

Once this works, then I intend to ExecuteSQLTask inside a ForEachLoopContainer.

推荐答案

您所需要的只是一个带有OLEDB源代码和OLE DB命令转换的数据流任务。

All you need is a Dataflow Task with an OLEDB source and an OLE DB Command transformation.

OLEDB源从要在其上执行逐行存储过程的表中进行选择。

The OLEDB source SELECTs from the table that you want to perform the row-by-row stored procedure on.

然后是一个OLE DB命令转换将调用存储过程，并将列从数据流传递到存储过程的参数。

It then is followed by an OLE DB Command transformation that calls the stored procedure and passes columns from the data flow to the parameters of the stored procedure.

这将导致存储过程每行执行一次你桌子上的您不需要初始的Execute SQL，也不需要For-Each循环。

This will result in the stored procedure being executed once for each row of your table. You don't need the initial Execute SQL, and you don't need the For-Each loop.

这篇关于在SSIS中为表中的每一行执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！