SSIS Catalog Object parameter information script

SSIS Catalog Object parameter information script

The first query is filtered out for information and the second query is a select * gets you all and you can change to your needs

USE SSISDB

SELECT pr.name AS [ProjectName]
, pr.description AS [ProjectDescription]
, pr.last_deployed_time AS [ProjectLastValidated]
, pr.validation_status AS [ProjectValidationStatus]
, op.object_name AS [PackageName]
, op.design_default_value AS [DefaultConnectionString]
,pr.deployed_by_name
FROM [internal].[object_parameters] op
INNER JOIN [internal].[projects] pr
ON pr.project_id = op.project_id
AND pr.object_version_lsn = op.project_version_lsn
WHERE op.parameter_name LIKE ‘%.ConnectionString’ — used to get connection string
ORDER BY DefaultConnectionString

USE SSISDB

SELECT *
FROM [internal].[object_parameters] op
INNER JOIN [internal].[projects] pr
ON pr.project_id = op.project_id
AND pr.object_version_lsn = op.project_version_lsn