SQL Server 2019：通过存储过程执行SSIS包时设置变量报错求助

问题描述 #

我尝试执行存储在文件服务器上的SSIS包文件（dtsx）。在SSISDB目录中，已有文件夹dynamiFoldertest和项目dynamiPackagetest。
使用存储过程catalog.deploy_packages将带有包变量的外部包dynaPackage.dtsx加载到该项目中，部署成功，可在路径dynamiFoldertest => dynamiPackagetest => dynaPackage.dtsx下看到该包。
随后调用catalog.create_execution启动包执行，成功获取ExecutionId。但调用catalog.set_execution_parameter_value为包变量设置值（@object_type设为30）时，每个变量都报错：

The parameter {variable name} does not exist or you don't have sufficient permissions

检查internal.execution_parameter_values和catalog.execution_parameter_values，未找到任何包变量记录。已确认具备部署权限，仅需存储过程解决方案，不使用.NET相关集成。

可能的原因及存储过程解决方案 #

1. 包变量未标记为“可配置” #

SSIS包中的变量只有设置为可配置时，才会被注册到SSISDB的参数列表中，允许通过catalog.set_execution_parameter_value修改。

• 验证变量可配置性：

SELECT 
    pv.name AS variable_name,
    pv.is_configurable
FROM 
    catalog.package_variables pv
JOIN 
    catalog.packages p ON pv.package_id = p.package_id
JOIN 
    catalog.projects pr ON p.project_id = pr.project_id
JOIN 
    catalog.folders f ON pr.folder_id = f.folder_id
WHERE 
    f.name = 'dynamiFoldertest'
    AND pr.name = 'dynamiPackagetest'
    AND p.name = 'dynaPackage';

若is_configurable为0，需重新编辑包将目标变量的**“配置”属性**设为True，再重新部署。

2. catalog.create_execution调用未指定具体包名 #

如果调用时未明确指定包名称，执行上下文可能指向项目而非具体包，导致无法识别包级变量。

• 正确调用示例：

DECLARE @execution_id BIGINT;
EXEC catalog.create_execution
    @folder_name = 'dynamiFoldertest',
    @project_name = 'dynamiPackagetest',
    @package_name = 'dynaPackage.dtsx', -- 必须指定具体包名
    @execution_id = @execution_id OUTPUT;

3. 变量名称大小写或拼写不匹配 #

SSISDB对变量名称大小写敏感，需确保传入的变量名与包中定义完全一致（包括大小写、下划线等）。

• 查询包内变量的准确名称：

SELECT name AS variable_name
FROM catalog.package_variables pv
JOIN catalog.packages p ON pv.package_id = p.package_id
WHERE p.name = 'dynaPackage'
AND p.project_id = (
    SELECT project_id 
    FROM catalog.projects 
    WHERE name = 'dynamiPackagetest' 
    AND folder_id = (SELECT folder_id FROM catalog.folders WHERE name = 'dynamiFoldertest')
);

直接使用查询结果中的名称作为参数传入catalog.set_execution_parameter_value。

4. 部署方式导致元数据同步不完整 #

使用catalog.deploy_packages部署单个包时，可能出现元数据同步不完整，导致包变量未注册到SSISDB参数表。

• 刷新包元数据：

EXEC catalog.validate_package
    @folder_name = 'dynamiFoldertest',
    @project_name = 'dynamiPackagetest',
    @package_name = 'dynaPackage.dtsx';

执行后重新创建执行上下文并尝试设置变量。若问题持续，建议将包打包为ISPAC项目文件，改用catalog.deploy_project部署以确保元数据完整。

5. 权限不足（部署权限≠参数修改权限） #

部署权限不代表拥有修改包参数的权限，需确保账号拥有ssis_admin角色，或被授予对应权限。

• 验证当前账号权限：

SELECT 
    dp.permission_name,
    dp.state_desc
FROM 
    catalog.database_permissions dp
JOIN 
    catalog.principals p ON dp.grantee_principal_id = p.principal_id
WHERE 
    p.name = CURRENT_USER
    AND dp.object_type IN (10, 20, 30); -- 文件夹、项目、包级权限

• 缺失权限时的授予语句：

GRANT EXECUTE ON OBJECT::catalog.set_execution_parameter_value TO [你的账号];
GRANT READ, EXECUTE ON CATALOG::dynamiFoldertest/dynamiPackagetest/dynaPackage TO [你的账号];

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