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...

推荐答案

我尝试处理像您这样的任务的另一种方法是人为地为父表生成 ID 字段.这里的想法是提前知道 ID，以便您可以分配外键值.

Another approach I would try in with task like yours is to artificially generate the ID field for the parent table. The idea here is knowing the ID ahead so you can assign the foreign key values.

然后不是使用多播，而是按顺序加载数据:父级，然后是子级.对于父表，勾选保留标识属性(OLEDB 目标).

Then instead of using multicast, load the data sequentially: parent, and then child. For the parent table, tick the Keep Identity property (OLEDB Destination).

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