SQL Agent作业中SSIS包间歇性出现Cannot Acquire Connection错误求助

问题背景 #

归属SQL Agent作业的多个SSIS包出现间歇性错误：有时步骤1/包1运行正常，次日却触发错误。包逻辑为创建Excel文件后写入数据，已设置DelayValidation = true。

完整错误日志 #

Executed as user: RDH\deltekvision. Microsoft (R) SQL Server Execute Package Utility  Version 15.0.4326.1 for 64-bit                                
Copyright (C) 2019 Microsoft. All rights reserved.    Started:  1:17:14 AM  Error: 2023-12-27 01:17:15.11                                
Code: 0xC0202009     Source: VP - OpportunityPeriod Connection manager "DestinationConnectionExcel"     Description: SSIS Error Code DTS_E_OLEDBERROR.                               
An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  S                                
ource: "Microsoft Access Database Engine"  Hresult: 0x80004005  Description: "External table is not in the expected format.".                                
End Error  Error: 2023-12-27 01:17:15.12     Code: 0xC020801C     Source: Data Flow Task 1 Destination - Query [2]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.                             
The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009.                             
There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2023-12-27 01:17:15.14                             
Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: Destination - Query failed validation and returned error code 0xC020801C.                               
End Error  Error: 2023-12-27 01:17:15.15     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2023-12-27 01:17:15.15                                
Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.                                
End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:17:14 AM  Finished: 1:17:15 AM  Elapsed:  0.75 seconds.  The package execution failed.  The step failed

核心错误分析 #

错误根源是External table is not in the expected format（外部表格式不符合预期），触发OLEDB连接失败，进而导致数据流任务验证失败。结合间歇性出现的特征，排除静态格式问题，重点排查动态创建Excel过程中的异常。

排查与解决方案 #

• 检查Excel文件创建后的状态
  • 确认创建Excel的任务（脚本任务/Execute Process Task）是否存在未完全释放文件句柄的情况，可在创建任务后添加3-5秒延迟（如脚本中System.Threading.Thread.Sleep(3000)），确保文件完全写入磁盘
  • 清理目标路径下的同名残留文件，避免前一次执行失败导致的文件损坏干扰新文件创建
• 64位与32位兼容性调整
  • 在SQL Agent作业步骤中勾选「使用32位运行时」，ACE驱动的32位版本对Excel格式兼容性更稳定
  • 确认服务器已安装与运行时匹配的ACE驱动版本，避免驱动缺失或版本冲突
• 连接管理器配置优化
  • 确保Excel连接管理器的版本设置（如Excel 2016）与创建的文件格式（.xlsx/.xls）完全匹配
  • 禁用连接管理器的RetainSameConnection属性，避免跨任务复用连接引发资源冲突
• 权限与路径校验
  • 确认SQL Agent服务账号RDH\deltekvision对目标文件夹拥有完全控制权限，防止权限不足导致文件创建不完整
  • 改用UNC路径（如\\server\share\file.xlsx）替代映射驱动器或相对路径，避免路径解析异常
• 执行上下文差异排查
  • 对比手动执行与SQL Agent执行的环境差异，检查环境变量、驱动器映射等是否存在不一致情况

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