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

博主：克罗西丁
发布时间：2025 年 10 月 09 日
382 次浏览
暂无评论
3839字数
分类： SQL

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

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

## 问题背景

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

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

1.  **先删除 `CW_Mould_Main` 仓库表中的旧数据：**
    ```sql
    -- 原始的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` 仓库表 ：**
    ```sql
    -- 原始的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` 条件变为：
**结束日期**: 
```sql
case when Len(@EndDate)>0 then @EndDate else GETDATE()-1 end
```
在4月1日执行时，结果是 `3月31日`。

**起始日期**: 
```sql
dateadd(month, datediff(month, 0, GETDATE()-1), 0)
```
这个复杂的表达式实际上是在计算`GETDATE()-1` (即`3月31日`) 所在月份的**第一天**，结果....既然是 `3月1日`!

那么所以，在 `4月1日` 时 `DELETE` 语句的实际效果是：
```sql
DELETE FROM CW_Mould_Main WHERE fd_BillDate BETWEEN '2024-03-01' AND '2024-03-31'
```
**结论：它删除了整个三月份的数据。**

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

#### 2. 分析INSERT语句的行为
`INSERT` 语句中的 `单据日期` 是通过
```sql
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` 逻辑，使其在没有参数时，精确地将删除范围限定在**昨天**。

```sql
--- 修改后的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` 统一，也改为**昨天**。
```sql
--- 修改后的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作业时，保证操作逻辑一致性的重要性。“删除昨天，插入昨天”的模式不仅解决了跨月数据丢失的问题，更重要的是它确保了数据同步任务的**安全性**、**可靠性**和**可维护性**。这是每一个数据工程师都应该掌握的核心设计原则。

最后修改：2025 年 10 月 09 日

喜欢就请我喝一杯奶茶吧~

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

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

克罗西丁 • 2025 年 10 月 09 日

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

## 问题背景

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

## 根本原因分析

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

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

> 啊....?

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

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

## 解决方案

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

#### 1. 修正DELETE语句
修改 `ELSE` 逻辑，使其在没有参数时，精确地将删除范围限定在**昨天**。

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

## 延伸思考与最佳实践

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

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

结果可能估计会更严重:

1.  任何一天的数据都只会在表中存在24小时。例如，`4月2日` 插入的数据，在 `4月3日` 任务开始时就会被 `DELETE` 语句（删除昨天的数据）清除掉。

2.  如果任务在同一天内重复执行，`DELETE` 操作因找不到“昨天”的数据而失效，但 `INSERT` 操作会再次插入“今天”的数据，造成数据重复。

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

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

小格自用公告逼乎体

Linux 安装 Koishi

OTA更新包提取Payload.bin

MAC Bigsur 挂载NTFS移动硬盘

Android隐藏状态栏/导航栏ADB命令

Javascript数组/嵌套循环/iife

Linux 自建RustDesk开源远程桌面中继服务器

Ubuntu解决MCBE找不到libssl.so.1.1共享库的错误

Navicat查看导出的数据库密码

借助FRP内网穿透教程

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

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

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

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款