SSIS结果集与存储过程并行送入 [英] SSIS result set fed in parallel to stored procedure

问题描述

我目前有2个存储过程...

I currently have 2 stored procedures ...

Stored Proc#1根据一系列条件返回产品列表

Stored Proc #1 returns a list of products based on a range of criteria

Stored Proc#2接受单个productID，并执行一些复杂的查询逻辑，并将结果写到表中

Stored Proc #2 accepts a single productID and performs some complex query logic and writes the results to a table

我的问题是，有超过30,000种产品，并且一次从存储的proc#1遍历结果集太慢，我无法轻松地将整个批处理调用集成到存储的#2

My problem is that there are over 30,000 products and going through the result set from stored proc #1 one at a time is too slow and I can't easily integrate an entire batch call into stored procedure #2

我的问题如下:使用SSIS可以设置一种任务，该任务将从存储过程1的结果集中获取并在每一行中并行调用存储过程2的结果. /p>

My question is as follows: Using SSIS is there a way to setup a task that would take the result set from Stored Procedure #1 and call Stored Procedure #2 in parallel with each row.

EXAMPLE of Stored Proc 1 result set
-----------------------------------
Product ID
----------
ABC123
XYZ987
AAABBB
CCCDDD
EEEFFF

I need to setup SSIS to call the following in parallel:
-------------------------------------------------------
EXEC StoredProc2 'ABC123'
EXEC StoredProc2 'XYZ987'
EXEC StoredProc2 'AAABBB'
EXEC StoredProc2 'CCCDDD'
EXEC StoredProc2 'EEEFFF'

推荐答案

好了，这是SP#2的许多执行.你可以做到的.

Ouch, that's many executions of SP #2. You can do it though.

创建数据流任务.将OLE DB源组件添加到数据流.编辑该组件并将其配置为执行SP#1.您可以在编辑器中查看列，以查看组件将输出什么.

Create a data flow task. Add an OLE DB Source component to the data flow. Edit the component and configure it to execute SP #1. You can view the columns in the editor to see what will be output from the component.

添加一个OLE DB命令数据流转换组件.创建从第一个组件到OLE DB Command组件的链接.编辑组件，然后在组件属性"选项卡上的SqlCommand属性中输入将运行SP#2的SQL语句.在SP的每个参数中都包括问号(?).

Add an OLE DB Command data flow transformation component. Create a link from the first component to the OLE DB Command component. Edit the component and enter the SQL statement that will run SP #2 in the SqlCommand property on the Component Properties tab. Include question marks (?) for each parameter for the SP.

选择列映射"选项卡.将输入列与每个参数相关联.

Select the Column Mappings tab. Associate an Input Column with each parameter.

这篇关于SSIS结果集与存储过程并行送入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！