SSIS包部署至SQL Server后PostgreSQL连接失败及部署流程咨询

问题概述 #

开发了MySQL到PostgreSQL的数据迁移SSIS包，本地运行正常，但部署到SQL Server并通过代理调度作业时，无法连接PostgreSQL服务器，已在目标服务器安装所需驱动，错误日志如下：

Executed as user: ERQCINRDB08\SSISUser. Microsoft (R) SQL Server Execute Package Utility Version 14.0.1000.169 for 32-bit Copyright (C) 2017 Microsoft. All rights reserved.
Started: 1:22:27 PM
Error: 2023-07-20 13:22:27.85
Code: 0xC0016016
Source: MySql_To_PG_file
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC0014020
Source: MySql_To_PG_file Connection manager "10.107.3.6.demo.postgres"
Description: An ODBC error -1 has occurred. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC0014009
Source: MySql_To_PG_file Connection manager "10.107.3.6.demo.postgres"
Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. End Error

Error: 2023-07-20 13:22:27.91
Code: 0x0000020F
Source: Data Flow Task 1 ODBC Destination [40]
Description: The AcquireConnection method call to the connection manager 10.107.3.6.demo.postgres failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC0047017
Source: Data Flow Task 1 SSIS.Pipeline
Description: ODBC Destination failed validation and returned error code 0x80004005. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC004700C
Source: Data Flow Task 1 SSIS.Pipeline
Description: One or more component failed validation. End Error

Error: 2023-07-20 13:22:27.91
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:22:27 PM Finished: 1:22:27 PM Elapsed: 0.156 seconds. The package execution failed. The step failed.

问题排查步骤 #

1. 优先解决密码解密错误（0xC0016016） #

这是核心触发点，本地加密的敏感信息（如密码）无法在服务器上解密：

• 更改SSIS包的保护级别：
  • 改为EncryptSensitiveWithPassword：设置一个密码，部署时输入该密码，确保服务器能解密敏感字段
  • 改为DontSaveSensitive：不在包中保存密码，通过SSIS环境变量、配置文件或SQL Server代理凭据传递密码
• 避免使用EncryptAllWithUserKey：该级别依赖本地用户的加密密钥，服务器上的ERQCINRDB08\SSISUser无法解密本地用户加密的内容

2. 验证ODBC驱动位数匹配 #

错误日志显示使用32位DTExec运行，需确保：

• 目标SQL Server上已安装32位PostgreSQL ODBC驱动（即使服务器是64位，SSIS代理默认可能调用32位运行时）
• 若使用64位运行时，需在作业步骤中取消勾选"使用32位运行时"，并确保安装64位驱动

3. 检查连接与权限配置 #

• 确认PostgreSQL服务器的pg_hba.conf已添加目标SQL Server的IP地址，允许其访问；同时检查PostgreSQL防火墙规则放行对应端口（默认5432）
• 验证连接字符串参数：服务器地址、数据库名、用户名是否正确，密码是否正确传递到连接管理器
• 运行作业的用户ERQCINRDB08\SSISUser需具备PostgreSQL数据库的访问权限，或使用有对应权限的代理账号运行作业

PostgreSQL迁移SSIS包完整部署流程 #

1. 开发阶段准备 #

• 设置包保护级别为EncryptSensitiveWithPassword或DontSaveSensitive，避免硬编码密码的加密兼容性问题
• 本地测试包运行正常，验证数据迁移逻辑、连接配置无误

2. 目标服务器环境配置 #

• 安装32位+64位PostgreSQL ODBC驱动，覆盖不同运行时需求
• 配置PostgreSQL服务器：
  • 修改pg_hba.conf，添加目标SQL Server的IP段（如host all all 192.168.x.x/32 md5）
  • 重启PostgreSQL服务使配置生效
  • 创建并授权迁移专用数据库用户，确保具备源MySQL读取、目标PostgreSQL写入权限

3. SSIS包部署 #

• 使用SSIS部署向导，将包部署到SQL Server的SSISDB目录
• 若包使用EncryptSensitiveWithPassword，部署过程中输入设置的密码，确保服务器能解密敏感信息
• （可选）创建SSIS环境变量，存储PostgreSQL连接字符串，包中引用环境变量而非硬编码配置

4. SQL Server代理作业配置 #

• 创建代理账号：关联具备PostgreSQL访问权限的凭据（Windows账号或SQL账号）
• 创建作业步骤：
  • 类型选择"SQL Server Integration Services Package"
  • 指定SSISDB中部署的包路径
  • 若需要32位运行时，勾选"使用32位运行时"（对应错误日志中的运行环境）
• 配置作业调度规则，测试作业运行，验证连接与数据迁移正常

内容的提问来源于stack exchange，提问作者RONIT WAJE.