How to identify what SSIS package(s) use a given Environment Variable? 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!
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%'