====== Project & package parameters in the SSIS catalog ======

This query enables you to inspect package and project parameters for objects deployed into the SSIS catalog:

<code tsql>SELECT
  op.parameter_id
, f.[name] AS folder_name
, p.[name] AS project_name
, CASE op.object_type
    WHEN 20 THEN 'Project'
    WHEN 30 THEN 'Package'
  END AS ParameterType
, op.[object_name]
, op.parameter_name
, CASE op.value_type
    WHEN 'V' THEN 'Literal'
    WHEN 'R' THEN 'Env var reference'
    ELSE op.value_type
  END AS SpecifiedAs
, op.design_default_value AS DesignTimeValue -- value set in Visual Studio at deployment time
, op.sensitive
--**************************************************************************
-- values as displayed in SSMS configure dialog
, IIF(LEFT(op.[parameter_name], 3) = 'CM.', 'Connection Managers', 'Parameters') AS [ConfigurationPage]
, op.[object_name] AS Container
, IIF(
    LEFT(op.[parameter_name], 3) = 'CM.'
  , SUBSTRING(op.[parameter_name], 4, CHARINDEX('.', op.[parameter_name], 4) - 4)
  , op.[parameter_name]
  ) AS [Name]
, IIF(
    LEFT(op.[parameter_name], 3) = 'CM.'
  , SUBSTRING(op.[parameter_name], CHARINDEX('.', op.[parameter_name], 4) + 1, LEN(op.[parameter_name]))
  , ''
  ) AS [PropertyName]
, op.default_value AS [ConfiguredValue]  -- value set in catalog post-deployment
--**************************************************************************
-- values for use in catalog.set_object_parameter_value 
-- https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-object-parameter-value-ssisdb-database
, op.[object_type]
, f.[name] AS folder_name
, p.[name] AS project_name
, op.[parameter_name]
, op.[object_name]
, op.value_type
--, op.*
FROM SSISDB.[catalog].[object_parameters] op
  INNER JOIN SSISDB.[catalog].projects p  ON p.project_id = op.project_id
  INNER JOIN SSISDB.[catalog].folders f ON f.folder_id = p.folder_id
--WHERE op.value_set = 1  -- design-time value overridden post-deployment
ORDER BY
  [ConfigurationPage] DESC
, op.object_type
, op.[object_name]
, [Name]
</code>

===== Setting parameter values in TSQL =====
You can set a parameter value post-deployment (i.e. the value of ''object_parameters.default_value''((A value configured //after// deployment is still considered a default because it can be overridden again at runtime.))) using the stored procedure ''catalog.set_object_parameter_value''. e.g. to set the value of default value of parameter 'MyParam' in package 'MyPackage.dtsx':
<code tsql>EXEC catalog.set_object_parameter_value
  @object_type = 30  -- package
, @folder_name = N'MyFolder'
, @project_name = N'MyProject'
, @parameter_name = N'MyParam'
, @parameter_value = 'post-deployment default value'
, @object_name = 'MyPackage.dtsx'</code>
The ''@object_name '' parameter is only required when ''@object_type'' = 30 -- an ''@object_type'' of 20 indicates a project parameter, and the project is already identified by the ''@project_name '' parameter.
{{tag> ssis catalog parameters}}