[转]SSIS OLE DB Source中执行带参数的存储过程

本文转自：http://www.cnblogs.com/michaelxu/archive/2009/10/21/1587450.html

问题描述：执行一个存储过程得到一个多条记录的结果集，然后循环这个结果集的每一条记录，根据这条记录的某几个字段执行一个带参数的存储过程，再将返回的结果集插入到一张表中。

 

 

 

 

 

Execute SQL Task “Get master records”中通过执行一个存储过程得到一个主结果集，Foreach Loop Container循环每条主记录，Data Flow Task对每条记录进行处理。

1、  先定义变量，如下表

 

 

 

 

v_BatchList定义为Object用来存储返回的主结果集。v_ID, v_OptionBinary, v_OptionField以及v_OptionTable是主结果集中的部分字段，用于Data Flow Task中执行存储过程时的输入参数。

2、  双击Get master records Task，打开配置窗口，在General页一定要将ResultSet设置为Full result set，即返回整个结果集，如下图。

 

 

 

 

切换到Parameter Mapping页，将主结果集中返回的字段按需要map到相应的定义好的变量上。我们已经定义了四个变量（v_ID, v_OptionBinary, v_OptionField以及v_OptionTable）用于参数输入，记得Direction设为Output。重要的一点Parameter Name一定要是数字，并以0开始与主结果集中的字段相对应。如我的主结果集返回如下：

 

 

 

 

那么Parameter Name的对应关系为：

v_ID:  3                      //因为字段ID在结果集中3的位置

v_OptionTable: 0       //因为字段optionTable在结果集中0的位置

v_OptionField: 1        //因为字段optionField在结果集中1的位置

v_OptionBinary: 8     //因为字段optionBinary在结果集中8的位置

这个关系一定要对应好，不然得不到数据。

 

 

 

再切换到Result Set页，增加一个Result，Result Name一定又要设置成0，Variable Name为我们之前定义好的v_BatchList。

 

 

 

 

 

 

3、  双击Foreach Loop Container，打开设置窗口，切换到Collection页，将Enumerator设置为Foreach ADO Enumerator以循环结果集。ADO object source variable选择我们之前定义好的v_BatchList。选择Rows in the first table，如下图。

 

 

 

 

 

 

再切换到Variable Mappings页，配置变量的对应关系，如下图。其中Index跟前面的配置要对应。

 

 

 

 

 

 

 

4、  双击Data Flow Task，切换到Data Flow页面，这里只有两个控件，一个OLE DB Source和一个OLE DB Destination，分别用来进行数据的取出和插入，如图。

 

 

 

 

 

Get option records是根据前面设置的四个参数执行一个存储过程，来得到一个子记录集。

 

 

 

5、  双击Get option records Task，设置好Connection Manager，注意Data access mode选择SQL command用以执行存储过程，记得存储过程要在开头加上下面两句：

SET FMTONLY OFF;

SET NOCOUNT ON;。

执行存储过程语句：

EXEC [dbo].[ConvertOptionBits2Collection] ?, ?, ?,?

?代表输入的参数，这个存储过程需要四个参数。点击Parameter，打开参数设置，增加四个输入参数，注意Parameters的名字一定要与存储过程中的定义相同，Variables选择我们已经定义好的变量，这些变量道这里已经有值了，需要传入到存储过程中。另外要注意的是参数添加的顺序也要与存储过程中的定义相同，不然对应不上。或者可以在执行存储过程的语句把参数名加上。

 

 

 

 

 

 

 

 

设置完成后，点击OK完成。

6、  双击OLE DB Destination，对其进行设置，选择好Connection Manager和要插入的表，主要是Columns的对应关系，设置好之后，就可以导数据了。