一次SSIS跨月数据丢失问题的排查与总结

这是一篇关于数据处理（ETL）的实战学习日志。记录了在处理一个SSIS计划包时，遇到的“跨月数据被清空”问题的排查过程，以及对ETL设计模式的深入理解。

问题背景

公司内有一个SSIS计划包，用于每日将源业务表 tb_Mould_Main 的数据同步到数据仓库表 CW_Mould_Main 中。当不设置 @StartDate 和 @EndDate 参数时，它会执行默认的每日同步逻辑。

这份SSIS计划的核心步骤如下：

1. 先删除 CW_Mould_Main 仓库表中的旧数据：

   -- 原始的DELETE语句
   delete from CW_Mould_Main
   where fd_BillDate between
       Convert(nvarchar(11), dateadd(month, datediff(month, 0,
           case when Len(@StartDate) > 0 then @StartDate else GETDATE()-1 end
       ), 0), 120)
       and
       case when Len(@EndDate) > 0 then @EndDate else GETDATE()-1 end

2. 再从 tb_Mould_Main 源表中插入新数据到 CW_Mould_Main 仓库表 ：

   -- 原始的INSERT语句
   select
       MouldNo 模具编号,
       --, 其他字段省略
       DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) AS 单据日期,
       GETDATE() AS 创建时间
   from tb_Mould_Main

现象：当每个月1号执行这个SSIS包后，CW_Mould_Main 表中上个月的所有数据都被清空了，但新插入的数据却是当月的。这导致了上个月数据的永久性丢失!

根本原因分析

为了找到问题的根源，我模拟了以4月1日这一天执行任务为例进行分析。

1. 分析DELETE语句的行为

在不带参数的情况下，DELETE 语句的 WHERE 条件变为：
结束日期:

case when Len(@EndDate)>0 then @EndDate else GETDATE()-1 end

在4月1日执行时，结果是 3月31日。

起始日期:

dateadd(month, datediff(month, 0, GETDATE()-1), 0)

这个复杂的表达式实际上是在计算GETDATE()-1 (即3月31日) 所在月份的第一天，结果....既然是 3月1日!

那么所以，在 4月1日 时 DELETE 语句的实际效果是：

DELETE FROM CW_Mould_Main WHERE fd_BillDate BETWEEN '2024-03-01' AND '2024-03-31'

结论：它删除了整个三月份的数据。

我只能扣: 6! 不愧是同事写的代码! :)

2. 分析INSERT语句的行为

INSERT 语句中的 单据日期 是通过

DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

生成的。
在 4月1日 执行时，这个表达式计算的是 GETDATE() (即4月1日) 所在月份的最后一天，结果是 4月30日。

啊....?

结论：生草的是, 所有新插入的数据，其 fd_BillDate 字段都被插入成了 4月30日。

3. 症结所在

将以上两步结合起来，真相大白：
在4月1日这一天，脚本删除了三月份的所有数据，然后插入了一批日期为四月份的数据。它并没有重新插入任何三月份的数据，所以在跨月时从而造成了“上个月数据被凭空清空”的现象，估计是前同事测每日测没有问题，但没有关注跨月问题导致的。

所以嘛...问题的本质是 DELETE 和 INSERT 操作的默认时间范围不一致。

解决方案

其实呢，对于每日同步任务，最佳实践是“同步昨天的数据”。因为在每天凌晨（公司设置的是凌晨7点执行），昨天一整天的数据已经尘埃落定，是进行数据快照的理想时间点。

基于此原则，我对代码进行如下修正：

1. 修正DELETE语句

修改 ELSE 逻辑，使其在没有参数时，精确地将删除范围限定在昨天。

--- 修改后的DELETE语句
DELETE FROM CW_Mould_Main
WHERE fd_BillDate BETWEEN
    -- 起始日期: 如果@StartDate为空, 则取"昨天"
    CASE
        WHEN Len(ISNULL(@StartDate, '')) > 0
        THEN @StartDate
        ELSE CONVERT(NVARCHAR(10), GETDATE() - 1, 120) -- 修改点
    END
    AND
    -- 结束日期: 如果@EndDate为空, 则取"昨天"
    CASE
        WHEN Len(ISNULL(@EndDate, '')) > 0
        THEN @EndDate
        ELSE CONVERT(NVARCHAR(10), GETDATE() - 1, 120) -- 修改点
    END

这样，默认情况下 WHERE 条件变为 fd_BillDate BETWEEN '昨天' AND '昨天'，等效于 fd_BillDate = '昨天'，同时不影响原先需要通过参数 @StartDate 和 @EndDate 指定日期的功能。

2. 修正INSERT语句

将 单据日期 的生成逻辑与 DELETE 统一，也改为昨天。

--- 修改后的INSERT语句
SELECT
    Partnum 编号,
    -- 其他字段省略...
    CONVERT(NVARCHAR(10), GETDATE() - 1, 120) AS 单据日期, -- 修改点
    GETDATE() AS 创建时间
FROM tb_Mould_Main

现在，DELETE 和 INSERT 在默认执行时，都精确地操作昨天的数据，实现了安全的“刷新”操作。

延伸思考与最佳实践

每日执行流程是怎样的？

采用新方案后，任务的每日执行流程是一个清晰的“滚动刷新”：

• 4月2日执行：删除 4月1日 的数据，插入最新的数据并标记为 4月1日。3月31日 的数据保持不变。
• 4月3日执行：删除 4月2日 的数据，插入最新的数据并标记为 4月2日。4月1日 和 3月31日 的数据保持不变。
  CW_Mould_Main 表因此成为了一张记录每日业务快照的历史表。

致命陷阱：为何不能“删除昨天，插入今天”？

或许可以再思考一个问题: 如果我们将INSERT的日期改为GETDATE()（今天），会怎么样?

结果可能估计会更严重:

1. 任何一天的数据都只会在表中存在24小时。例如，4月2日 插入的数据，在 4月3日 任务开始时就会被 DELETE 语句（删除昨天的数据）清除掉。
2. 如果任务在同一天内重复执行，DELETE 操作因找不到“昨天”的数据而失效，但 INSERT 操作会再次插入“今天”的数据，造成数据重复。

总结

通过这次问题排查，我深刻理解了在设计ETL作业时，保证操作逻辑一致性的重要性。“删除昨天，插入昨天”的模式不仅解决了跨月数据丢失的问题，更重要的是它确保了数据同步任务的安全性、可靠性和可维护性。这是每一个数据工程师都应该掌握的核心设计原则。