SSIS包偶发卡在Pre-Execute阶段长时间停滞问题排查求助

问题背景 #

• 业务需求：从超大型源数据库拉取销售交易数据
• 现有SSIS包执行逻辑分为两个节点：
  1. Execute SQL Task：创建带唯一聚集索引的全局临时表，存储待拉取的transactionId集合
  2. Data Flow Task：关联上一步创建的全局临时表编写Select查询拉取交易数据，写入另一台SQL Server实例的目标表

故障特征 #

• Data Flow Task长时间卡在Pre-Execute阶段
• 源服务器执行sp_whoisactive排查时，未观测到超大型源库上有对应查询在运行
• SSIS Catalog执行日志显示，任务输出Load staging table from HumongousDB:Information: Pre-Execute phase is beginning.后停滞超过7小时，而该包正常全流程执行时长通常不超过1小时
• 故障为偶发，无固定复现规律

已完成的前置配置 #

• Data Flow Task、源端/目标端连接管理器的DelayValidation属性已设置为True
• Data Flow Task内源组件、目标组件的ValidateExternalMetaData属性已设置为False

运行环境信息 #

• SSIS Catalog所在SQL Server版本：Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64)
• 宿主机操作系统：Windows Server 2012 Datacenter（Build 9200，Hypervisor虚拟化环境）

根因定位步骤 #

1. 开启SSIS Verbose级日志
   默认的Information级别日志不会记录Pre-Execute阶段的子步骤执行情况，切换到Verbose级别后可以看到参数绑定、连接申请、元数据拉取每个环节的具体耗时，直接定位卡滞的具体子步骤。卡滞时通过Process Explorer抓取对应ISServerExec.exe进程的线程调用栈，可以确认阻塞点是在SSIS引擎内部、连接层还是元数据锁等待环节。
2. 排查全局临时表锁残留
   卡滞时直接在tempdb中查询sys.dm_tran_locks视图，确认是否有历史执行残留的会话持有同名全局临时表的元数据锁。全局临时表的特性是所有引用它的会话全部断开后才会自动删除，如果之前失败的包执行没有正常清理连接，残留的全局临时表会持有元数据锁，新包执行时Pre-Execute阶段校验表结构会被阻塞——这个阻塞发生在SSIS引擎的元数据读取环节，不会向源库发起实际业务查询，完全匹配sp_whoisactive看不到活动查询的现象。
3. 排查连接池复用异常
   SSIS默认会复用OLE DB连接池中的缓存连接，如果缓存连接对应的全局临时表已经被销毁，但连接本身未被回收，Pre-Execute阶段通过这个失效连接访问临时表时会触发内部重试逻辑，这个过程同样不会产生可被sp_whoisactive捕获的活动查询。
4. 核对已知版本Bug
   SQL Server 2016 SP2 CU15存在多个已确认的SSIS数据流引擎缺陷，涉及元数据校验阶段的内部死锁、临时表对象引用计数错误，会直接导致Pre-Execute阶段无响应。

可落地的规避方案 #

1. 改造全局临时表逻辑
   放弃固定命名的全局临时表，每次包执行时通过SSIS变量拼接执行实例GUID生成唯一临时表名，动态传递给数据流源的查询语句，从根源上避免历史残留表的锁冲突。同时在创建临时表前增加清理逻辑，示例代码如下：

   IF OBJECT_ID('tempdb..##tmp_TransactionIds') IS NOT NULL
   BEGIN
       DECLARE @killStmt NVARCHAR(1000)
       DECLARE lock_cursor CURSOR FOR
       SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
       FROM sys.dm_tran_locks
       WHERE resource_id = OBJECT_ID('tempdb..##tmp_TransactionIds')
       OPEN lock_cursor
       FETCH NEXT FROM lock_cursor INTO @killStmt
       WHILE @@FETCH_STATUS = 0
       BEGIN
           EXEC sp_executesql @killStmt
           FETCH NEXT FROM lock_cursor INTO @killStmt
       END
       CLOSE lock_cursor
       DEALLOCATE lock_cursor
       DROP TABLE ##tmp_TransactionIds
   END
   -- 后续执行原有建表、建索引逻辑
   

2. 调整连接配置
   将源端、目标端OLE DB连接管理器的RetainSameConnection属性设置为True，强制包执行全程使用同一个物理连接，避免连接上下文切换导致的临时表丢失问题。也可以在连接字符串中添加OLE DB Services = -2;参数，关闭OLE DB连接池复用，彻底规避缓存连接带来的异常。
3. 升级版本补丁
   将SQL Server 2016实例升级到SP2 CU17及以上版本，修复已知的SSIS Pre-Execute阶段卡滞Bug。
4. 增加超时熔断
   给Data Flow Task设置2小时的执行超时阈值，触发超时后自动终止异常执行实例，避免残留会话长期占用锁资源阻塞后续任务。

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