如何通过SSIS执行脚本传递变量至包内SQL任务并正常运行

一、核心问题修正点 #

你当前的问题出在两个关键环节：SSISDB执行脚本的参数类型/名称错误，以及SQL任务的变量引用配置不当。以下是完整的修正与配置流程：

二、SSIS包端配置 #

1. 包变量配置 #

• 打开SSIS包，找到User::Duration变量，确保：
  • 数据类型为Smallint（与传递的参数值类型匹配）
  • ReadOnly属性设为False（允许外部传入值）

2. SQL任务配置 #

• 编辑SQL任务的SQL语句，使用占位符?引用包变量：

  DECLARE @TIME DATETIME = DATEADD(mi, -?, GETDATE());
  -- 在此添加后续使用@TIME的业务逻辑
  

• 切换到参数映射选项卡，添加映射规则：
  • 变量名：User::Duration
  • 方向：输入
  • 数据类型：Smallint
  • 参数名称：0（对应SQL语句中第一个?的索引）

三、SSISDB执行脚本修正 #

修正参数设置部分的object_type和@parameter_name，确保指向包变量：

USE SSISDB;

DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
    @package_name=N'PackageName.dtsx',
    @folder_name=N'Projects',
    @project_name=N'ProjectName',
    @use32bitruntime=False,
    @reference_id=Null,
    @execution_id=@execution_id OUTPUT

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
      @execution_id,
      @object_type=50,
      @parameter_name=N'LOGGING_LEVEL',
      @parameter_value=@var0

-- 修正：设置包变量User::Duration，object_type=30对应包级变量
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
      @execution_id,
      @object_type=30,
      @parameter_name=N'User::Duration',
      @parameter_value=10080 /*1周，值需在Smallint范围(-32768~32767)内*/

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

说明：object_type取值规则：50=系统参数，20=项目参数，30=包级变量

四、Windows计划任务触发配置 #

1. 创建新计划任务，操作选择启动程序
2. 程序/脚本填写：sqlcmd.exe
3. 添加参数（根据身份验证方式选择）：
   • SQL身份验证：-S 你的SQL服务器名 -U 用户名 -P 密码 -i "D:\Scripts\ExecuteSSISPackage.sql"
   • Windows身份验证：-S 你的SQL服务器名 -E -i "D:\Scripts\ExecuteSSISPackage.sql"
4. 设置触发器的执行时间，确保SQL Server代理与SSIS服务处于运行状态

五、验证流程 #

1. 手动执行修正后的SSISDB脚本，查看SSISDB的catalog.executions视图确认执行状态
2. 触发计划任务，检查包内SQL任务是否正确使用User::Duration变量生成预期的@TIME值

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