代码之家 › 专栏 › 技术社区 › Chris KL

集成服务和标识列

ssis sql-server

Chris KL · 技术社区 · 15 年前

我有点像一个SSIS新手,虽然整个系统看起来很简单,但在概念上我不理解在这个场景中需要经历的过程:

需要将发票和发票行表从源数据库映射到目标数据库中具有不同标识值的两个等效表。
对于插入的每个发票,我需要获取分配给它的标识,然后插入引用该新标识的所有行。
发票(发票号)上有一个替代键,但是这些替代键也可能与目标系统中的发票号冲突,因此它们也必须重新编号。

这肯定是集成中的一个常见场景——是否有一个通用的解决方案?

2 回复 | 直到 15 年前

onupdatecascade 15 年前

克里斯·克-你是对的,这比人们想象的要难。我有三种方法,适用于不同的情况:

如果要加载的数据很小(数百或数千但不是数十万),则可以这样做:使用一个OLEDB命令,该命令为每个父行执行一次插入,并返回标识值;然后从该命令的下游将输出从该行连接到子行,并插入它们。优点:直观。缺点:鳞片严重。这个方法在网络上有文档记录,你可以用谷歌搜索。
如果我们谈论的是一个更大的系统,您需要批量加载,那么还有两种风格:

a.如果您在加载期间对该表具有独占访问权(实际上是独占的,以某种方式强制执行),那么您可以从该表中获取最大现有ID,使用ssis脚本任务对从该最大ID以上开始的行进行编号,然后设置identity insert on,填充它们,并设置identity insert off。然后在ssis中让脚本生成的键分配给子行。优点:快速简单,一次到数据库。缺点:如果其他进程同时插入到您的表中,则可能出现错误。易碎。

B.如果您没有独占访问权,那么我知道的唯一方法就是往返数据库,因此:插入所有父行,但跟踪不是标识列(例如业务键)的父行。在第二个数据流中,通过使用使用业务密钥来获取父ID的查找转换来处理子记录。确保与缓存相比,查找得到了适当的调整,并且对业务密钥进行了索引。

Joe Harris 15 年前

好吧,恐怕这是个好消息/坏消息。首先是好消息和一些你可能知道的背景,但我会把它记下来,以防你不知道。

你 通常地 不能插入 任何东西 到标识列中。当然,就像生活中的其他事情一样,有时候你需要这样做,这可以通过身份插入选项来完成。

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable (
       MyIdCol,
       Etcâ¦
       ) 
SELECT SourceIdCol,
       Etcâ¦
  FROM MySourceTable

    SET IDENTITY_INSERT MyTable OFF

现在,你说你在目标中有代理键,但是你说它们可能会冲突。所以我有点困惑您是在使用源代码中的键(如标识列)还是在目标中生成新的键?我强烈建议不要尝试在单个键列中合并键空间。如果您需要保留这些键,那么我建议使用像sourcesystemid这样的多字段键来保持它们的唯一性。

最后一个坏消息是:ssis不提供使用identity_insert选项的简单方法。唯一能做到这一点的方法是在插入任务之前执行的SQL任务中打开它。您应该能够将表名作为变量传递到脚本中。确保随后包含另一个SQL任务以将其关闭,因为一次只能在一个表上使用。