SSIS:使用多播将数据输入2个相关目的地 [英] SSIS : Using multicast to enter data into 2 RELATED destinations

问题描述

我是SSIS的新手.我有来自单一来源的数据.我需要将该数据输入几个表中(这些表由外键关系关联).我正在使用多播将数据输入到多个目的地.

I am new to SSIS. I have data coming from a single source. I need to enter that data into several tables (these tables are related by foreign key relationships). I am using multicast to enter the data into several destinations.

我的问题是...

如何获取进入一个目的地的条目的身份并将该身份用于第二个目的地的外键列?

How do I get the identity of an entry into one destination and use that identity for the foreign key column of the 2nd destination?

这里是我正在寻找的示例. Employee表具有Address表的外键.但是来源包括所有这些信息.将数据输入2个不同的位置很容易.但是，当我在Employee表中输入信息时，我需要Address表中的身份.我如何获得该ID?

Here is an example of what I am looking for. The Employee table has a foreign key to the Address table. But the source includes all this information. Entering the data into 2 different locations is easy. But I need the identity from the Address table when I enter the info into the Employee table. How do I get that Id?

来源(Excel文件)

Name        Street           State        etc...
----        ------           -----
Jim         12345 Plain St.  CA
Bob         54321 Main St.   CA
etc.

目的地

   Address        Employee
   -------        -------
PK AddressId   PK EmployeeId
   Street      FK AddressId
   State          Name
   etc...         etc...

推荐答案

简短的答案是，开箱即用，SSIS并不是为此而构建的.将数据放到表中后，目标组件将不允许发送输出流.

The short answer is that, out of the box, SSIS isn't built for that. Once you land data in a table, the destination components don't allow for an output stream to be sent.

您可以使用OLE DB Command来伪造此行为，但是由于它会为流经数据流的每一行发出单例插入语句，因此您的性能将不佳.通常，引擎尝试分批处理N个工作单元，并对数据执行批量的，基于集合的操作，以获取更大的吞吐量.您也可以使用Script Component来执行此操作.同样的性能警告仍然适用.

You could fake this behaviour out by using an OLE DB Command but your performance will be less than good since it will issue a singleton insert statement for every row that flows through the data flow. Generally, the engine attempts to batch N units of work up and perform bulk, set-based operations on the data to get more throughput. You could also use a Script Component to perform this. The same performance caveat would still apply.

更好的选择可能是将数据放入暂存表中，然后在Data flow之后使用Execute SQL Task并使用

A better option might be to land your data into a staging table and then use an Execute SQL Task after your Data flow and use the OUTPUT clause of the INSERT operation to capture those identities and then patch them into your other table.

这篇关于SSIS:使用多播将数据输入2个相关目的地的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！