sql server—如何在T/ sql中设置变量时执行来自Integration Services Catalog的S

Question

我最近继承了一个数据库，其中包含一个存储过程，该存储过程使用xp_cmdshell运行SSIS包，同时向包添加单个用户变量(下面的代码)。作为我们迁移到SQL Server 2016(从SQL Server 2008R2)的一部分，我试图更改此存储过程，而不是从名为SSISDB的集成服务目录中调用包。我遇到的问题是，我似乎无法弄清楚如何以一种它能识别的方式将该变量传递给包!

旧代码:

DECLARE @Report_id INT = 63936
   ,@result INT
   ,@dtsExecCmd VARCHAR(4000)
   ,@params INT
   ,@path VARCHAR(500);
DECLARE @sif_report_object_ref_id VARCHAR(32)
   ,@error_count INT;
SET @result = 0;
SET @path = 'E:OldServerFilesDeploymentPackage.dtsx';
SET @params = @Report_id; 
SET @dtsExecCmd = 'dtexec /F ' + @path; 
SET @dtsExecCmd = @dtsExecCmd
    + ' /SET "package.Variables[User::Report_Id].Properties[Value]";""'
    + CAST(@params AS VARCHAR(10)) + '""';
EXEC @result = [sys].[xp_cmdshell] @dtsExecCmd, [no_output];
PRINT @result;

这是我到目前为止所做的，它执行，但使用Report_id参数的默认值:

DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'TestProject', @project_name=N'TestReportETLPackage', @use32bitruntime=False, @reference_id=NULL
SELECT @execution_id
DECLARE @var0 SMALLINT = 3
DECLARE @ReportId INT = 63936
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
-- something goes in here for User::Report_Id as a package variable...
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Answer 1

您应该用一个参数替换这个变量，这样它就可以从外部直接设置，并且包开发人员清楚地知道它应该以这种方式提供。

作为一个临时修复，您应该会发现下面的东西可以工作。

EXEC [catalog].[set_execution_property_override_value]
  @execution_id = @execution_id,
  @property_path = 'package.Variables[User::Report_Id].Properties[Value]',
  @property_value = @Report_id,
  @sensitive = 0;