SSISDB : check variable set for each tasks

Question

in ssisdb , are there any view which stored the corresponding variable set/get for each of the task inside a ssis package (using TSQL)?
the following is similar but for parameters only i think

Answer 1

Hi @sakuraime ,

Check below code which get all projects/packages parameter default value and referenceVariable.

 SELECT     fldr.name AS FolderName  
        ,objp.[referenced_variable_name] AS [EnvironmentVariable]  
        , proj.name AS ProjectName  
        , COALESCE('Package: ' + pkg.name, 'Project') AS Scope  
        , objp.parameter_name COLLATE Latin1_General_CS_AS AS ParameterName  
        ,Objp.design_default_value   
        ,Objp.referenced_variable_name  
        ,(select top 1 Ev.value as VariableValue from SSISDB.[internal].[environment_variables] EV where ev.name=Objp.referenced_variable_name) as [value]  
FROM SSISDB.catalog.folders AS fldr  
   INNER JOIN  SSISDB.catalog.projects proj  
            ON proj.folder_id = fldr.folder_id  
    Left JOIN SSISDB.catalog.object_parameters objp      
        ON objp.project_id = proj.project_id  
    LEFT JOIN SSISDB.catalog.packages pkg  
        ON objp.object_name = pkg.name  
        AND objp.project_id = pkg.project_id  
--WHERE proj.name like '%XXX%'  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @sakuraime ,
With above code, it will return the environment variables.

If you want to check the value of the user variables, I think it couldn't find with tsql.

You can view the User-defined variable value change information in SSISDB All Executions report, but at first you have to enable the Raise Change Event for each variable at design time.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.