SSIS包部署执行咨询：加密连接串适配多环境及变量传参

问题1：加密连接串适配多环境及作业运行时解密 #

• 推荐使用**SSIS目录环境变量（项目部署模式）**管理连接串：
  • 将包部署到SSIS目录后，创建对应环境（开发/测试/生产），在环境中定义连接串变量，敏感信息（密码）会被SSIS目录自动加密存储。
  • 配置SQL Server代理作业时，选择对应的环境引用，作业运行时会自动加载环境中的连接串并解密敏感部分，无需手动输入密码。
• 若使用包部署模式：
  • 包加密方式选择EncryptSensitiveWithPassword或EncryptAllWithPassword，在SQL Server代理作业的“SSIS包步骤”中，找到“配置”选项卡，填写包密码，作业运行时会自动解密敏感连接信息。
  • 多环境适配可通过不同的作业步骤配置（或不同作业）分别对应不同环境的连接串，或使用SSIS配置文件（XML/注册表），作业步骤指定对应环境的配置文件路径。

问题2：启动作业时传递SSIS变量值 #

• EXEC msdb.dbo.sp_start_job本身不支持直接传递SSIS变量值，可通过以下方式实现：
  1. SSIS目录参数映射（项目部署模式）：
     • 在作业的SSIS包步骤中，进入“参数”选项卡，将SSIS包的变量映射到作业参数或环境变量。
     • 若需动态传参，可在启动作业前，通过T-SQL修改环境变量的值（需具备SSIS目录的修改权限）：

       UPDATE SSISDB.catalog.environment_variables
       SET value = '新变量值'
       WHERE environment_id = 你的环境ID
         AND variable_name = '你的变量名'
       

       之后再执行sp_start_job启动作业。
  2. 使用dtexec命令行调用：
     • 通过xp_cmdshell执行dtexec命令，直接指定变量值，示例：

       EXEC xp_cmdshell 'dtexec /ISSERVER "\SSISDB\你的项目名\你的包名.dtsx" /Server "你的SQL实例名" /Set \Package.Variables[User::你的变量名].Value;"要传递的值"'
       

       注意需提前启用xp_cmdshell，且执行账户需有SSIS包的执行权限。
  3. 配置表传参：
     • 创建一个参数配置表，SSIS包启动时从该表读取变量值。
     • 启动作业前，先通过T-SQL更新配置表的参数值，再执行sp_start_job，包运行时会自动读取最新值。

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