如何通过SSIS将SQL Server分配的Run_ID回写更新Oracle对应表

前置准备 #

提前在SSIS包中配置两个连接管理器：

• Oracle连接管理器：指向源Oracle数据库，需确保账号有对应表的查询、更新权限
• SQL Server连接管理器：指向目标SQL Server数据库，需确保账号有对应表的插入、查询权限
  同时创建SSIS包变量User::Current_RUN_ID，类型设为数值型，用于存储本次批次的唯一RUN_ID。

实现步骤（全部放在同一SSIS控制流中） #

1. 生成批次唯一RUN_ID（可选，根据SQL Server侧RUN_ID生成规则调整） #

如果SQL Server侧RUN_ID不是表自增字段，先添加执行SQL任务，连接选择SQL Server连接管理器，执行语句生成全局唯一RUN_ID，例如：

-- 示例：从批次号序列取新的RUN_ID，可根据实际业务调整
SELECT NEXT VALUE FOR SEQ_PO_RUN_ID AS NEW_RUN_ID

将查询结果的单值赋值给变量User::Current_RUN_ID。

2. 数据流任务：抽取Oracle NEW_PO数据导入SQL Server #

添加数据流任务，内部组件配置如下：

• OLE DB源：使用Oracle连接管理器，查询语句为：
  SELECT Record_ID, PO_Type, PO_NUM, DateTime FROM 你的Oracle_PO表名 WHERE PO_Type = 'NEW_PO' AND Run_ID IS NULL
• 派生列转换：新增两个派生列：
  • SYSTEM字段：固定值为'ORDER'
  • RUN_ID字段：值为变量User::Current_RUN_ID（如果SQL Server是自增生成RUN_ID则不需要加这个字段）
• OLE DB目标：使用SQL Server连接管理器，选择目标导入表，完成字段映射。

  如果RUN_ID是SQL Server表自增生成，导入完成后新增执行SQL任务，执行SELECT MAX(RUN_ID) FROM 你的SQL Server_PO表名 WHERE 导入时间 >= DATEADD(minute,-5,GETDATE())（时间范围可根据实际批次执行时长调整），将结果赋值给User::Current_RUN_ID。

3. 数据流任务：回写RUN_ID到Oracle并更新PO_Type #

添加第二个数据流任务，和前序任务用成功优先级约束连接，内部组件配置如下：

• OLE DB源：使用SQL Server连接管理器，查询语句为：
  SELECT RECORDID, RUN_ID FROM 你的SQL Server_PO表名 WHERE RUN_ID = ?
  参数映射中，将参数0绑定到变量User::Current_RUN_ID。
• OLE DB命令转换：使用Oracle连接管理器，执行更新语句：
  UPDATE 你的Oracle_PO表名 SET Run_ID = ?, PO_Type = 'Processed_PO' WHERE Record_ID = ? AND PO_Type = 'NEW_PO'
  参数映射中，参数0绑定源字段RUN_ID，参数1绑定源字段RECORDID。

可选优化（针对大数据量场景） #

如果单次处理的PO记录超过1000条，不建议使用OLE DB命令逐行更新，可调整回写逻辑：

• 先将待更新的RECORDID、RUN_ID数据批量插入Oracle的临时表
• 新增执行SQL任务（Oracle连接），执行批量更新语句：

  MERGE INTO 你的Oracle_PO表名 T
  USING Oracle临时表 S
  ON (T.Record_ID = S.Record_ID AND T.PO_Type = 'NEW_PO')
  WHEN MATCHED THEN UPDATE SET T.Run_ID = S.RUN_ID, T.PO_Type = 'Processed_PO'
  

  批量更新性能远高于逐行更新。

事务一致性保证 #

将所有任务放入同一个序列容器中，做如下配置避免数据不一致：

• 序列容器的TransactionOption属性设为Required
• Oracle和SQL Server两个连接管理器的RetainSameConnection属性均设为True
  配置完成后，整个流程要么全部执行成功，要么全部回滚，不会出现SQL Server导入成功但Oracle回写失败的情况。

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