每日SSIS导入Excel至SQL表，如何记录数据变更至Changelog表？

原方案失效的原因 #

1. 触发器未触发的本质：勾选“删除目标表中的行”后，SSIS执行逻辑是先全量删除目标表所有数据，再全量插入Excel中的新数据——整个过程没有UPDATE操作，因此针对UPDATE的触发器完全不会被触发。
2. 触发器逻辑错误：即使存在UPDATE操作，你的触发器CTE逻辑也有问题：
   • t2中SELECT * FROM INSERTED EXCEPT SELECT * FROM INSERTED会得到空集，后续INTERSECT操作结果也是空，不会写入任何NEW状态的日志；
   • t1的逻辑冗余，DELETED EXCEPT INSERTED INTERSECT DELETED等价于DELETED EXCEPT INSERTED，写法没必要复杂化。

更优实现方案 #

针对你的场景（仅少量数据变更，全量删插效率低且无法记录变更），推荐采用增量同步+变更日志记录的方案，以下两种方式任选：

方案一：使用SQL MERGE语句（推荐） #

核心思路是先将Excel数据导入临时/ staging表，再通过MERGE语句同步到目标表，同时在同步过程中写入变更日志。

1. 创建Staging表：和目标表结构完全一致，用于临时存储每日导入的Excel数据：

CREATE TABLE dbo.Sheet1_Staging (
    -- 与[dbo].[Sheet1$]完全相同的字段结构，包含主键
    ID INT PRIMARY KEY,
    Column1 VARCHAR(50),
    Column2 DATETIME,
    -- 其他字段...
);

2. 修改SSIS包：将Excel数据导入Sheet1_Staging表，取消“删除目标表中的行”选项，改为每次导入前清空Staging表（执行TRUNCATE TABLE dbo.Sheet1_Staging）。
3. 执行MERGE同步并记录日志：

MERGE INTO dbo.[Sheet1$] AS Target
USING dbo.Sheet1_Staging AS Source
ON Target.ID = Source.ID -- 用主键匹配
WHEN MATCHED AND EXISTS (
    -- 判断字段是否有变化，避免无意义的更新
    SELECT Target.* EXCEPT SELECT Source.*
) THEN
    UPDATE SET 
        Target.Column1 = Source.Column1,
        Target.Column2 = Source.Column2,
        -- 更新其他字段...
    -- 记录旧数据
    OUTPUT DELETED.*, GETDATE() AS ChangedAt, 'UPDATED_OLD' AS [Status] INTO dbo.changelog
WHEN NOT MATCHED BY Target THEN
    INSERT (ID, Column1, Column2, ...)
    VALUES (Source.ID, Source.Column1, Source.Column2, ...)
    -- 记录新增数据
    OUTPUT INSERTED.*, GETDATE() AS ChangedAt, 'NEW' AS [Status] INTO dbo.changelog
WHEN NOT MATCHED BY Source THEN
    DELETE
    -- 记录删除数据
    OUTPUT DELETED.*, GETDATE() AS ChangedAt, 'DELETED' AS [Status] INTO dbo.changelog;

注：如果changelog表结构和目标表不一致，需要调整OUTPUT的字段列表，确保和changelog表匹配。

方案二：修改SSIS包逻辑 #

通过SSIS内置组件实现增量同步和日志记录：

• 取消“删除目标表中的行”选项；
• 添加查找转换：将Excel源数据与目标表数据按主键关联；
• 拆分三个分支：
  1. 无匹配的行（新增）：直接插入目标表，同时插入changelog（状态为NEW）；
  2. 有匹配但数据不同的行（修改）：先插入目标表的旧数据到changelog（状态UPDATED_OLD），再更新目标表，最后插入新数据到changelog（状态UPDATED_NEW）；
  3. 目标表存在但Excel中不存在的行（删除）：先将该行数据插入changelog（状态DELETED），再从目标表删除。

额外优化建议 #

• 为目标表和Staging表的主键建立非聚集索引（如果没有主键则先添加主键），提升匹配和同步效率；
• 变更日志表可只记录主键、变更字段、变更时间和状态，无需存储整行数据，减少存储空间占用；
• 每日同步前，可对目标表或changelog表做增量备份，防止数据丢失。

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