SSIS增量插入报错：主键约束冲突问题排查求助

从你的描述和SQL语句来看，虽然你确认源数据无错误，但SQL逻辑和字段匹配上存在两个关键问题，导致了主键约束违反的错误：

1. INSERT与SELECT字段完全不匹配（数量+顺序错位） #

仔细对比你的INSERT目标字段列表和SELECT返回字段，会发现：

• INSERT指定了16个字段，其中第8位是custodian_id，但SELECT语句里完全没有包含这个字段，只返回了15个字段。
• 这直接导致后续所有字段整体向前偏移一位：原本应该插入到sr_id（INSERT第13位）的值，实际被填充成了SELECT里的acc_number；sr_type被填充成SELECT里的sr_id，以此类推。

这种错位会让主键字段sr_id被错误的值填充，而这些错误值很可能和已存在的channel_id、cust_reg_id组合重复，最终触发主键冲突。

2. lookup_channel关联可能产生多匹配记录 #

你的临时表通过channel_name关联lookup_channel获取channel_id，如果lookup_channel表中存在**同一个channel_name对应多个不同channel_id**的情况，临时表的单条记录会被关联出多条不同channel_id的结果。即使源数据本身没有重复，这些多匹配生成的记录中，可能存在与dim_cust_reg已存在的(channel_id, sr_id, cust_reg_id)组合重复的情况，甚至生成重复的组合（如果lookup_channel里有重复的channel_name+channel_id记录）。

修复方案 #

先修正字段匹配问题（最紧急） #

确保SELECT字段的数量、顺序和INSERT目标字段完全一致。如果cust_reg_dim_stg表本身有custodian_id字段，就补充到SELECT中；如果该字段不需要插入或允许为空，就从INSERT字段列表中移除它。

修正后的SQL示例（假设临时表有custodian_id）：

insert into dim_cust_reg WITH(TABLOCK) 
( 
    channel_id,
    cust_reg_id,
    cust_id,
    status,
    date_created,
    date_activated,
    date_archived,
    custodian_id,
    reg_type_id,
    reg_flags,
    acc_name,
    acc_number,
    sr_id,
    sr_type,
    as_of_date,
    ins_timestamp 
) 
select 
    channel_id,
    cust_reg_id,
    cust_id,
    status,
    date_created,
    date_activated,
    date_archived,
    stg.custodian_id,  -- 补充缺失的字段
    reg_type_id,
    reg_flags,
    acc_name,
    acc_number,
    sr_id,
    sr_type,
    as_of_date,
    getdate() ins_timestamp 
from umpdwstg..cust_reg_dim_stg stg with(nolock) 
join lookup_channel ch with(nolock) 
    on stg.channel_name = ch.channel_name 
where not exists (
    select * 
    from dim_cust_reg dest 
    where dest.cust_reg_id=stg.cust_reg_id 
        and dest.sr_id=stg.sr_id 
        and dest.channel_id=ch.channel_id 
)

确保lookup_channel关联的唯一性 #

检查lookup_channel表，建议给channel_name添加唯一约束，避免同一名称对应多个channel_id。如果业务上确实需要保留多channel_id的场景，可以通过窗口函数取指定的channel_id（比如最新的）：

join (
    select channel_name, channel_id,
           row_number() over(partition by channel_name order by create_date desc) as rn
    from lookup_channel
) ch with(nolock) 
    on stg.channel_name = ch.channel_name 
    and ch.rn = 1  -- 只取每个channel_name对应的最新一条记录

可选：插入前排查重复 #

可以先运行以下查询，检查待插入数据中是否存在重复的主键组合，提前发现问题：

select channel_id, sr_id, cust_reg_id, count(*) as duplicate_count
from (
    select ch.channel_id, stg.sr_id, stg.cust_reg_id
    from umpdwstg..cust_reg_dim_stg stg with(nolock) 
    join lookup_channel ch with(nolock) 
        on stg.channel_name = ch.channel_name 
) temp
group by channel_id, sr_id, cust_reg_id
having count(*) > 1;

内容的提问来源于stack exchange，提问作者AswinRajaram