Access Catalog SSIS package variable from SSMS execution

Question

Hi,
we have all our SSIS packages in catalog. I need to execute this package in stored procedure and get 1 of the package variable value which gets it's value after running webservice in script task.
I am trying to get that value. But not able to. Some say by raising an event, can get information. I know that there is sql task to input those values into table and get it from table. I am trying to avoid it.

This is what I tried:
Added variable to ssis package and set it's Raise Change Event property to true.
Then, in script task code:
public void Main()
{

     string packageName = Dts.Variables["System::PackageName"].Value.ToString();  
     string taskName = Dts.Variables["System::TaskName"].Value.ToString();  
     string subComponent = packageName + "." + taskName;  
     int informationCode = 1001;  
     bool fireAgain = true;  
     Dts.Variables["User::rs"].Value = "trying my best";  
     string msg = "variable rs:" + Dts.Variables["User::rs"].Value.ToString();             
     Dts.Events.FireInformation(informationCode, subComponent, msg, string.Empty, 0, ref fireAgain);   
     Dts.TaskResult = (int)ScriptResults.Success;  
 }  

Execute SSIS package from SSMS. We can't use dts.exe utility
Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @display _name=N'folderName', @Shayna Webb _name=N'projectName', @use32bitruntime=False, @reference_id=Null

Select @execution_id

EXEC [SSISDB].[catalog].[start_execution] @execution_id

select * from SSISDB.CATALOG.EVENT_MESSAGES where operation_id = 45827 --@execution_id

I get message as 'Package:Value of variable [rs] is changed to .', but no value in that.
What am I doing wrong?

Please let me know.
Thank You

Answer 1

Hi @Spunny ,

It is definitely possible.
Andy Leonard shared two methods how to do it some years ago.

Check it out here: ETL-instrumentation-logging-SSIS-variable-values