如何用SSIS复制更新SQL数据且保留已迁移的删除数据？

针对你的需求——同步数据库A全表数据到B，支持新增、匹配主键的更新，同时保留B中A已删除的数据，以下是几种高效的SSIS实现方案：

方案1：动态生成Data Flow任务 #

手动为每个表创建Data Flow工作量大，可通过脚本任务动态生成数据流：

• 用脚本任务遍历数据库A的所有用户表，获取每张表的主键信息和列结构。
• 基于SSIS API（如Microsoft.SqlServer.Dts.Runtime）在脚本中动态创建Data Flow组件：
  • 配置源连接指向A库的当前表，目标连接指向B库的对应表。
  • 添加Lookup组件，以主键为匹配条件，判断B库是否存在该记录。
  • 分支处理：Lookup命中则走更新逻辑，未命中则走插入逻辑。
• 这种方式只需编写一次脚本，即可批量处理所有表，后续新增表也能自动适配。

方案2：T-SQL MERGE + SSIS执行SQL任务 #

同一服务器下，直接用数据库层面的MERGE语句效率远高于Data Flow，步骤如下：

• 编写动态T-SQL脚本，遍历A库的所有表，自动生成对应表的MERGE语句：

  DECLARE @TableName NVARCHAR(128)
  DECLARE TableCursor CURSOR FOR
  SELECT name FROM A.sys.tables WHERE type = 'U'
  
  OPEN TableCursor
  FETCH NEXT FROM TableCursor INTO @TableName
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @MergeSQL NVARCHAR(MAX)
      -- 获取主键列（需确保每张表都有主键）
      DECLARE @PKColumn NVARCHAR(128)
      SELECT @PKColumn = col.name FROM A.sys.tables t
      JOIN A.sys.indexes i ON t.object_id = i.object_id AND i.is_primary_key = 1
      JOIN A.sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
      JOIN A.sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
      WHERE t.name = @TableName
  
      -- 生成MERGE语句
      SET @MergeSQL = N'MERGE B.dbo.' + QUOTENAME(@TableName) + N' AS Target
      USING A.dbo.' + QUOTENAME(@TableName) + N' AS Source
      ON Target.' + QUOTENAME(@PKColumn) + N' = Source.' + QUOTENAME(@PKColumn) + N'
      WHEN MATCHED THEN
          UPDATE SET ' + 
          -- 动态生成更新列（排除主键）
          STUFF((SELECT N', Target.' + QUOTENAME(col.name) + N' = Source.' + QUOTENAME(col.name)
                 FROM A.sys.columns col
                 WHERE col.object_id = OBJECT_ID(N'A.dbo.' + @TableName) AND col.name != @PKColumn
                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N'
      WHEN NOT MATCHED BY Target THEN
          INSERT (' + 
          STUFF((SELECT N', ' + QUOTENAME(col.name)
                 FROM A.sys.columns col
                 WHERE col.object_id = OBJECT_ID(N'A.dbo.' + @TableName)
                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N')
          VALUES (' + 
          STUFF((SELECT N', Source.' + QUOTENAME(col.name)
                 FROM A.sys.columns col
                 WHERE col.object_id = OBJECT_ID(N'A.dbo.' + @TableName)
                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N');'
  
      EXEC sp_executesql @MergeSQL
      FETCH NEXT FROM TableCursor INTO @TableName
  END
  
  CLOSE TableCursor
  DEALLOCATE TableCursor
  

• 在SSIS中添加执行SQL任务，执行上述动态脚本即可完成全表的新增和更新，且不会删除B中A已删除的数据。
• 此方案性能最优，无需Data Flow的额外开销，开发量小，适合同一服务器的同步场景。

方案3：CDC（变更数据捕获）+ SSIS增量同步 #

如果需要定期增量同步而非全量，可启用A库的CDC功能：

• 开启A库的CDC，捕获每张表的新增、更新操作。
• 在SSIS中创建数据流，读取CDC捕获的变更数据，同步到B库（匹配主键更新，新增则插入）。
• 这种方式仅同步变更数据，大幅减少数据传输量，适合频繁同步的场景，但需额外配置CDC环境。

注意事项 #

• 确保所有需要同步的表都有主键，否则MERGE和Lookup无法实现匹配逻辑。
• 若需同步表结构变更，需在数据同步前先处理结构同步（如用T-SQL对比表结构并调整）。
• 建议添加事务控制，避免同步过程中出错导致数据不一致。

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