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.