指定在SSIS XML导入期间如何生成主键 [英] Specify how primary keys are generated during SSIS XML import

问题描述

问题是我将数据导入到关系表中，其中数据源是XML文件+ XSD架构。 XML源有几个输出，节点之间的关系由SSIS在列中创建为order_id（生成的主键值不在XML中），并且让我们说订单详细信息我们得到外键order_id。

The problem is I import data into relational tables where the source of the data is a XML-file + XSD schema. The XML source has several outputs and the relationships between nodes is created by SSIS in columns as order_id (generated primary key value not in XML) and in lets say order details we get the foreign key order_id.

文件被导入，我们在说出订单和订单详细信息之间有正确的参照完整性，但密钥在每个文件中只是唯一的，所以如果再次导入相同的文件同一个id由ssis生成/使用。

The file gets imported and we have a correct referential integrity between lets say orders and order details but the key is only unique within each file so if same file is imported again the same id is generated/used by ssis.

如何确保唯一性/控制id的生成方式。

How can you ensure uniqueness / control how the id is generated.

我试图在订单表中使用标识列作为order_id，但它不会传播到参考表，如订单详细信息......

I have tried to use a identity column as order_id in the order table that works but then it does not propagate down to reference tables like order details...

示例如果我们有

<order some attributes ...>
    <orderdetails some attributes ...></orderdetails> 
    <orderdetails some attributes ...></orderdetails> 
</order> 

如果我们只是让ssis导入行，我们会得到类似的结果：

if we just let ssis import the rows we will get something like :

订单表

order_id = 4，其余数据列

order_id = 4 , rest of data columns

orderdetails表

orderdetails table

第1行orderdetail列，order_id = 4
第2行orderdetail列，order_id = 4

row 1 orderdetail columns , order_id=4 row 2 orderdetail columns , order_id=4

一切都好，但我不明白如何生成显然半随机id = 4

Everything ok but I don't understand how the apparently semi-random id = 4 is generated

如果使用增量密钥，我会得到（假设种子从1开始）

If an incremental key is used I would get (assuming seed starts at 1)

订单表

order_id = 1，其余数据列

order_id = 1 , rest of data columns

orderdetails表

orderdetails table

第1行orderdetail列，order_id = 4< - 与先前生成的SSIS相同
第2行orderdetail列，order_id = 4< - 与之前生成的SSIS相同

row 1 orderdetail columns , order_id=4 <--same as previously SSIS generated row 2 orderdetail columns , order_id=4 <--same as previously SSIS generated

我希望外键的id为1，因此我想有一些中间步骤/设置我有错过了以保持参考导入XML数据时的完整性。

I would like the id be 1 for the foreign key in details so I guess there is some intermediary step / setting I have missed in order to maintain referential integrity when importing XML data.

任何指针都将受到高度赞赏。

Any pointers would be highly appreciated.

推荐答案

我建议您像往常一样导入标题，使用IDENTITY生成内部唯一键。您还应该将SSIS生成的密钥导入到同一个表中。

What I suggest is that you import the header as usual, generating internal unique keys using IDENTITY. You should also import the SSIS generated key into this same table.

现在将详细信息导入单独的登台表，再次保留SSIS生成的密钥。

Now import the details to a seperate staging table, again preserving the SSIS generated key.

现在使用原始标头表从SSIS生成的密钥映射到唯一密钥。

Now use the original header table to map from an SSIS generated key to a unique key.

为此，您需要更新空白您的临时表中的字段，如下所示：

To do this you update a blank field in your staging table with something like this:

UPDATE LineStaging
SET Unique_Key = Header.UniqueKey
FROM Header 
WHERE Header.SSISKey = LineStaging.SSISKey

现在你的Unique_Key字段包含正确的外键。您可以将这些分阶段记录复制到真实行表中：

Now your Unique_Key field contains the correct foreign key. You can copy those staged records accross to your 'real' Line table:

INSERT INTO行
SELECT * FROM LineStaging

INSERT INTO Line SELECT * FROM LineStaging

在SSIS中可能有一种方法可以实现这一点，但我更喜欢SQL方法。

There is probably a way to do this on the fly in SSIS but I prefer SQL methods.

这篇关于指定在SSIS XML导入期间如何生成主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！