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