Configuration - SQL Server Integration Services Design Patterns - page 415

with that name (and matching data type!) will be available when you go to select the en-
vironment to run the package in.
Default Parameter Values Using T-SQL
Default parameter values and connection manager properties can be set through the
SSIS Catalog's T-SQL API. This allows a DBA to automate the setting of parameter
values after a deployment or after a project is moved to a new SSIS Catalog. An easy
way to create a script is to make the changes through the parameter configuration UI,
and then click the Script button. Listing 17-1 shows the T-SQL used to set default val-
ues for a two items: a package parameter ( MaxCount ) is set to 100, and a connection
manager property ( CM.SourceFile.ConnectionString ) is set to
' C:\Demos\Data\RaggedRight.txt '.
Listing 17-1 . Setting Parameter Values Using T-SQL
DECLARE @var sql_variant
= N'C:\Demos\Data\RaggedRight.txt'
EXEC [SSISDB].[catalog].[set_object_parameter_value]
@object_type=20,
@parameter_name=N'CM.SourceFile.ConnectionString',
@object_name=N'ExecutionDemo',
@folder_name=N'ETL',
@project_name=N'ExecutionDemo',
@value_type=V,
@parameter_value=@var
GO
DECLARE @var bigint = 100
EXEC [SSISDB].[catalog].[set_object_parameter_value]
@object_type=30,
@parameter_name=N'MaxCount',
@object_name=N'LongRunning.dtsx',
@folder_name=N'ETL',
@project_name=N'ExecutionDemo',
@value_type=V,