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

最新推荐文章于 2021-04-02 23:50:47 发布

匆匆过客121

最新推荐文章于 2021-04-02 23:50:47 发布

阅读量3.2k

点赞数

分类专栏： SQL Server 文章标签：存储 ssis manager variables parameters object

SQL Server 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

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

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选择我们已经定义好的变量，这些变量道这里已经有值了，需要传入到存储过程中。另外要注意的是参数添加的顺序也要与存储过程中的定义相同，不然对应不上。或者可以在执行存储过程的语句把参数名加上。