how do I execute an SSIS package from Integration Services Catalog while setting a variable in T/SQL?

Question

I recently inherited a database that included a stored procedure that runs an SSIS package using xp_cmdshell while adding a single user variable to the package (code below). As part of our migration to SQL Server 2016 (from SQL Server 2008R2) I'm attempting to change this stored procedure to instead call the package from an Integration Services Catalog called SSISDB. The problem I'm running into is that I can't seem to figure out how to pass that variable to the package in a way that it recognizes!

Old code:

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:\OldServerFiles\Deployment\Package.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;

Here's what I have so far, and it executes but with the default value for the Report_id parameter:

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

Can you modify the package to change from using an SSIS variable to an SSIS parameter? That's the simplest/clearest way to pass values in from a calling process. — onupdatecascade
Currently I can't modify the package, but when I get SSDT to integrate with our software policy I will do so. — Elsimer

Martin Smith Martin Smith · Accepted Answer · 2016-08-08T18:32:21

You should replace this variable with a parameter so that it is straightforwardly settable from the outside and clear to package developers that it is expected to be supplied this way.

As a temporary fix though you should find that something like the following works.

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;

how do I execute an SSIS package from Integration Services Catalog while setting a variable in T/SQL?

1 Answers