如何在SSIS中实现增量加载以捕获当日新增/修改数据？

完全可行，增量加载是解决全量加载耗时过长的核心优化手段，针对你的百万行数据场景，以下是几种成熟的实现方式：

一、基于时间戳的增量加载（最易落地） #

• 前置条件：源表需包含记录新增/修改的时间戳字段（如CreateTime、UpdateTime），且该字段会随数据变更自动更新（比如通过触发器或业务逻辑维护）
• SSIS包改造步骤：
  1. 添加SSIS变量（如@LastLoadTime），用于存储上次成功加载的时间点，可将该值持久化到数据库配置表、本地配置文件或SSIS包配置中
  2. 修改源数据查询，仅筛选时间戳晚于@LastLoadTime的记录，示例查询：

     SELECT * 
     FROM SourceTable 
     WHERE CreateTime > ? OR UpdateTime > ?
     

     将查询参数绑定到@LastLoadTime变量
  3. 数据加载完成后，将@LastLoadTime更新为本次加载的结束时间（如GETDATE()），并同步到持久化存储位置
• 注意事项：需确保时间戳字段的更新逻辑无遗漏，比如批量更新操作必须触发时间戳变更，否则会漏掉修改记录

二、基于SQL Server变更数据捕获（CDC）（适合复杂变更场景） #

• 前置条件：源数据库为SQL Server，且具备开启CDC的权限
• SSIS包改造步骤：
  1. 先在源数据库为目标表开启CDC：

     EXEC sys.sp_cdc_enable_table 
     @source_schema = N'dbo', 
     @source_name = N'SourceTable', 
     @role_name = NULL
     

  2. 在SSIS包中使用CDC Source组件，配置对应的捕获实例、起始时间点（上次加载的LSN或时间），直接获取增量变更数据（包含新增、修改、删除操作）
  3. 通过CDC Splitter组件区分不同类型的变更，分别执行插入、更新、删除操作到目标表
• 优势：无需依赖业务时间戳，能精准捕获所有数据变更，包括批量操作带来的修改

三、基于哈希值的增量加载（无时间戳时的替代方案） #

• 前置条件：若源表无时间戳字段，可通过计算记录的哈希值识别变更
• SSIS包改造步骤：
  1. 在查询源数据时动态计算记录哈希值，或在源表/目标表中新增哈希字段存储该值，示例查询：

     SELECT *, 
            HASHBYTES('SHA2_256', CONCAT(ISNULL(Col1,''), ISNULL(Col2,''), ISNULL(Col3,''))) AS RowHash 
     FROM SourceTable
     

  2. 通过Lookup组件对比源数据与目标表的哈希值，筛选出哈希不一致的记录（即新增或修改的记录）
  3. 将差异记录加载到目标表，同时更新目标表的哈希值
• 注意事项：哈希计算会增加CPU开销，适合变更频率较低的场景；需处理NULL值的拼接逻辑，避免哈希计算错误

四、额外优化建议 #

• 使用Lookup组件时，根据目标表数据量选择合适的缓存模式：数据量适中用全缓存，数据量过大用部分缓存或无缓存
• 启用目标表的快速加载（Fast Load）选项，批量插入数据提升写入性能
• 将数据转换操作尽量前置到源查询中，减少SSIS内存中的数据处理量

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