SSISDB : check variable set for each tasks

sakuraime 2,276 Reputation points
2021-11-16T08:04:04.303+00:00

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

149705-image.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,862 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,598 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 18,896 Reputation points
    2021-11-16T09:14:43.437+00:00

    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.


  2. ZoeHui-MSFT 18,896 Reputation points
    2021-11-17T09:24:19.43+00:00

    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.

    No comments