How to identify what SSIS package(s) use a given Environment Variable?

Question

I suspect my Google skills are just insufficient today, but I'm looking for a quick TSQL script that will identify all SSIS packages in the Package Store that are using a given Environment Variable Name.

For instance, when I Configure a package and look at the Paramaters page, I can choose a given Environment Variable. In the following screen shot, the environment variable I'm concerned with is named SMTP. I want to find all packages using this environment variable so I can perform some investigation/testing before making any en masse changes.

If someone has a script readily available, I'd be grateful if you could post it, otherwise I'll post something after I code it up.

Thanks!

Answer 1

Since Environment Variables can be used at either the Project or Package level, this query will indicate said level the variable is used and quickly return the results I want. Hopefully this will come in handy for someone else in the future.

SELECT    objp.[referenced_variable_name] AS [EnvironmentVariable]
        , fldr.name AS FolderName
        , proj.name AS ProjectName
        , COALESCE('Package: ' + pkg.name, 'Project') AS Scope
        , objp.parameter_name COLLATE Latin1_General_CS_AS AS ParameterName
FROM SSISDB.catalog.object_parameters objp
    INNER JOIN SSISDB.catalog.projects proj
        ON objp.project_id = proj.project_id
    INNER JOIN SSISDB.catalog.folders AS fldr
        ON proj.folder_id = fldr.folder_id
    LEFT JOIN SSISDB.catalog.packages pkg
        ON objp.object_name = pkg.name
        AND objp.project_id = pkg.project_id
-- Only search Projects/Packages that reference Environment variables
WHERE objp.value_type = 'R'
    AND objp.referenced_variable_name LIKE '%SMTP%'

Answer 2

I have modified the above answers query to 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%'