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

i inherited database included stored procedure runs ssis package using xp_cmdshell while adding single user variable package (code below). part of our migration sql server 2016 (from sql server 2008r2) i'm attempting change stored procedure instead call package integration services catalog called ssisdb. problem i'm running can't seem figure out how pass variable package in way 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 varchar(10)) + '\""';  exec @result = [sys].[xp_cmdshell] @dtsexeccmd, [no_output]; print @result; 

here's have far, , executes default value 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 -- goes in here user::report_id package variable... exec [ssisdb].[catalog].[start_execution] @execution_id go 

you should replace variable parameter straightforwardly settable outside , clear package developers expected supplied way.

as temporary fix though should find 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;