SSIS父子表关联场景下如何配置CDC实现增量加载到平表

CDC本身确实是单表粒度的变更追踪机制，要覆盖你这个Left Join关联场景的所有增量变更，必须同时为Table1、Table2开启CDC，仅开单表一定会漏变更，整体实现逻辑如下：

前置准备 #

• 源库侧同时给Table1、Table2开启CDC能力，确认两张表的增、改、删操作都能正常被CDC捕获
• 首次同步先执行一次你原来的全量关联查询，把全量数据写入目标平表，同时记录本次全量同步完成后对应的CDC日志序列号（LSN）边界，作为后续增量同步的起始点位，这一步直接用SSIS自带的CDC控制任务就能完成，不需要手动写系统表查询。

增量同步处理逻辑 #

每个增量同步周期，先通过CDC控制任务拉取本次同步的LSN起止范围，再分两类变更场景分别处理，避免漏数：

• 场景1：子表Table1发生变更
  Table1的新增、字段更新、外键Table2Id修改、删除操作，都会直接改变关联后的平表结果。处理时直接拉取本周期内Table1的所有变更记录，关联当前最新版本的Table2表数据，按照操作类型对目标平表做对应写入：

  • 变更类型为插入/更新：将关联后的结果通过Merge操作写入目标平表，匹配到Table1主键就更新字段，未匹配就插入新行
  • 变更类型为删除：直接删除目标平表中对应Table1主键的记录即可

• 场景2：父表Table2发生变更
  哪怕Table1本身没有任何改动，只要Table2的字段发生变化，所有关联到这条Table2记录的Table1行，拼接出来的平表结果都会变，这也是只给Table1开CDC会漏数的核心原因。处理逻辑：

  1. 拉取本周期内Table2所有发生变更的主键Id集合
  2. 反查Table1中所有Table2Id落在这个集合内的行
  3. 将这些Table1行关联最新版本的Table2数据，全部通过Merge操作更新到目标平表即可
     如果你业务上Table2会做物理删除，需要额外捕获Table2的删除操作，按照你原来Left Join的逻辑，把目标平表里对应关联记录的Table2字段置空即可；如果是软删除，直接关联最新表数据就能拿到正确的软删状态，不需要额外处理。

核心查询参考 #

你可以直接在OLE DB源中使用参数化查询，参数绑定CDC控制任务输出的起止LSN变量即可：
处理Table1变更的查询语句：

-- 两个参数依次绑定：CDC起始LSN、CDC结束LSN
DECLARE @start_lsn binary(10) = ?, @end_lsn binary(10) = ?;

SELECT t1.*, t2.*
FROM cdc.fn_cdc_get_all_changes_dbo_Table1(@start_lsn, @end_lsn, 'ALL') t1_chg
INNER JOIN dbo.Table1 t1 ON t1.Id = t1_chg.Id
LEFT JOIN dbo.Table2 t2 ON t1.Table2Id = t2.Id
WHERE t1_chg.$operation IN (1,2,4) -- 对应操作：1=删除、2=插入、4=更新

处理Table2变更的查询语句：

-- 两个参数依次绑定：CDC起始LSN、CDC结束LSN
DECLARE @start_lsn binary(10) = ?, @end_lsn binary(10) = ?;

SELECT t1.*, t2.*
FROM cdc.fn_cdc_get_all_changes_dbo_Table2(@start_lsn, @end_lsn, 'ALL') t2_chg
INNER JOIN dbo.Table2 t2 ON t2.Id = t2_chg.Id
INNER JOIN dbo.Table1 t1 ON t1.Table2Id = t2.Id
WHERE t2_chg.$operation IN (2,4) -- 对应操作：2=插入、4=更新

注意事项 #

• 关联查询时一定要关联当前最新版本的业务表，不要直接关联CDC捕获到的变更快照，否则会把数据修改过程中的中间状态同步到目标端，导致数据不一致
• 每次增量同步完成后，记得通过CDC控制任务更新已同步的LSN点位，避免重复同步或者漏数
• 如果两张表的数据量很大，可以在Table1的Table2Id字段、两张表的主键字段上加索引，减少增量查询时的性能损耗

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