在SSIS包中，如何插入主从记录? [英] In an SSIS package, how do you insert master-detail records?

问题描述

我有一个数据流任务，该任务从一个数据库中提取数据并将其插入到SQL Server数据库中.在将每条记录插入主行之后，我还需要将行插入到明细表中.明细表的数据非常简单，可以计算.

I have a data flow task that extracts data from one database and inserts into a SQL Server database. After each record is inserted in the master row I also need to insert rows into a detail table. The data for the detail table is pretty simple and can be calculated.

1. 插入后如何检索标识列的值?
2. 如何产生必须在第二个表中插入的行?我可以在脚本中执行此操作吗?

我是否需要在控制流级别使用Foreach循环来转移数据流任务中的父行，然后再使用另一个Foreach循环来插入明细记录?

Do I need to use a Foreach loop at the control flow level which transfers the parent row in a data flow task, then have another Foreach loop which inserts the detail records?

我可以只在脚本中执行所有详细信息行插入吗?这可能比放入Foreach循环更容易.

Can I just perform all the detail row inserts in a script? That would probably be easier than putting in the Foreach loops.

推荐答案

这是一种方法.

创建类型为object的变量.

Create a variable of type object.

创建一个执行SQL任务"，该任务将获取您的源数据并将其加载到变量(ADO.NET)中.

Create a "Execute SQL Task" that grabs your source data and loads it into the variable (ADO.NET).

创建一个"ForEach循环容器".

Create a "ForEach Loop Container.

将成功连接器(绿色)从执行SQL任务"拖到"ForEach循环容器".将循环容器上的Enumerator更改为"foreach ADO Enumerator"，然后从"ADO对象源变量"中选择变量.

Drag the Success connector (green) from the "Execute SQL Task" to the "ForEach Loop Container". Change Enumerator on the loop container to "foreach ADO Enumerator" and pick your variable from the "ADO object source variable".

在循环中，您应该可以添加可以使用的执行SQL任务".

Within your loop, you should be alble to add an "Execute SQL Task" that you can work with..

插入主表后，应该可以使用SCOPE_IDENTITY()获取每个ID，然后将其插入到明细表中.

You should be able to use the SCOPE_IDENTITY() to get each ID after inserting into the master table and use that to insert into the detail table.

这篇关于在SSIS包中，如何插入主从记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！