SSIS派生列创建主键及SQL Server归档表构建技术问询

嘿，我正好做过类似的SQL Server归档+SSIS变更追踪的方案，给你详细说说怎么实现你的两个核心目标，结合你已经在用的派生列任务来扩展就行。

一、先搞定归档表的结构设计 #

要满足「还原指定时间段状态」和「查询变更记录」这两个需求，归档表不能只是原表的复制，得加几个关键的追踪字段。举个例子，假设你原表是dbo.OrderInfo，归档表dbo.OrderInfo_Archive可以这么建：

CREATE TABLE dbo.OrderInfo_Archive
(
    -- 原表所有字段照搬
    OrderId INT PRIMARY KEY,
    OrderNo VARCHAR(50),
    Amount DECIMAL(18,2),
    CustomerId INT,
    -- 新增追踪字段
    ChangeType CHAR(1) NOT NULL, -- 'I'=插入, 'U'=更新, 'D'=删除
    ChangeDateTime DATETIME2(3) NOT NULL, -- 精确到毫秒的变更时间
    ChangeBatchId UNIQUEIDENTIFIER NOT NULL, -- SSIS每次运行的批次ID，关联同批次变更
    IsCurrent BIT NOT NULL DEFAULT 1 -- 标记是否为当前有效记录
)

这些字段是实现需求的核心，别偷懒省掉哦。

二、基于你已有的SSIS派生列任务扩展 #

既然你已经加了派生列任务，咱们就顺着这个往下完善整个流程：

1. 先生成批次ID #

在SSIS包的最开头，加一个执行SQL任务，生成一个唯一的批次ID（用NEWID()），把它存在包变量@BatchId里（变量类型设为Guid或者String都可以）。
执行SQL任务的语句很简单：

SELECT NEWID() AS BatchId

记得把结果集映射到@BatchId变量。

2. 派生列任务补充追踪字段 #

在你现有的派生列组件里，新增这几个派生字段：

• ChangeType：先默认设为"I"（插入），后续更新/删除场景再单独调整
• ChangeDateTime：用SYSDATETIME()获取精确到毫秒的当前时间
• ChangeBatchId：直接引用包变量@[User::BatchId]
• IsCurrent：默认赋值1

3. 分场景处理变更（插入/更新/删除） #

要覆盖所有变更类型，推荐用查找组件搭配条件分支，或者如果你的SQL Server版本支持，开**CDC（变更数据捕获）**会更省心：

场景1：插入新记录

用查找组件对比原表和归档表的主键（比如OrderId），找不到匹配的就是新记录，直接把这些记录插入归档表，ChangeType保持"I"就行。

场景2：更新现有记录

查找组件找到匹配的记录，并且原表字段和归档表中IsCurrent=1的记录有差异时，需要做两步：

1. 用执行SQL任务把归档表中对应OrderId的旧记录IsCurrent设为0，同时更新ChangeDateTime和ChangeBatchId

   UPDATE dbo.OrderInfo_Archive
   SET IsCurrent = 0,
       ChangeDateTime = GETDATE(),
       ChangeBatchId = ?
   WHERE OrderId = ?
     AND IsCurrent = 1
   

   这里的?分别映射包变量@BatchId和当前行的OrderId。
2. 把更新后的新记录插入归档表，ChangeType设为"U"，IsCurrent=1。

场景3：删除记录

用查找组件找出「归档表中IsCurrent=1但原表中不存在」的记录，然后插入一条ChangeType="D"的记录到归档表（或者直接把旧记录的IsCurrent设为0并标记ChangeType="D"，看你需求）。

三、实现两个核心目标的查询语句 #

1. 还原指定时间段的表状态 #

比如要还原2024-05-20 15:30:00这个时间点的表状态，直接查归档表中「变更时间不晚于该时间且是当前有效记录」的数据：

SELECT OrderId, OrderNo, Amount, CustomerId
FROM dbo.OrderInfo_Archive
WHERE ChangeDateTime <= '2024-05-20 15:30:00'
  AND IsCurrent = 1

2. 查询指定时间段内的变更记录 #

要查2024-05-19到2024-05-20的所有变更，直接过滤时间范围即可：

SELECT *
FROM dbo.OrderInfo_Archive
WHERE ChangeDateTime BETWEEN '2024-05-19 00:00:00' AND '2024-05-20 23:59:59'
ORDER BY ChangeDateTime DESC, ChangeBatchId

四、一些实用优化建议 #

• 给归档表的ChangeDateTime、IsCurrent、主键字段建非聚集索引，不然数据量大了查询会很慢
• 在SSIS包里加个日志表，记录每次运行的BatchId、开始/结束时间、处理的插入/更新/删除记录数，方便后续排查问题
• 如果你的SQL Server版本支持（2008及以上），开启CDC功能是更高效的方式，SSIS直接读取CDC的变更日志表，不用自己写查找逻辑，准确性也更高

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