Get Selected Project Parameters for a Project from the SSIS Catalog

 USE SSISDB

GO

-- I'm planning on leaving the DEV values in the SSIS package and updating after deployment 

-- to the catalog

DECLARE 

@FOLDER_NAME NVARCHAR(128) = N'< folder name goes here >'

, @PROJECT_NAME NVARCHAR(128) = N'< project name goes here >';

/*

get the parameters for a project

object_type

20 project parameter

30 package parameter

object_name

when object_type = 20, project name

when object_type = 30, SSIS package name

*/

SELECT o.*

FROM [catalog].[folders] f

JOIN [catalog].[projects] p ON p.folder_id = f.folder_id

JOIN [catalog].[object_parameters] o ON o.[project_id] = p.[project_id]

WHERE f.[name] = @FOLDER_NAME

AND p.[name] = @PROJECT_NAME

-- project parameters

AND o.object_type = 20

AND (

-- just get specific parameter names

o.parameter_name LIKE N'%.ConnectionString' OR

o.parameter_name LIKE N'%.ServerName' 

)

;