title: how do I execute an SSIS package from Integration Services Catalog while setting a variable in T/SQL?
body: <p>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! </p>
  
  <p>Old code:</p>
  
  <pre><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;
  </code></pre>
  
  <p>Here's what I have so far, and it executes but with the default value for the <strong>Report_id</strong> parameter:</p>
  
  <pre><code>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
  </code></pre>