Updating Default SSIS Values in the SSIS Catalog

A couple friends recently contacted me trying to solve a similar issue: how to update parameter values stored in the SSIS Catalog. One of my friends was interested in migrating their SSIS Catalog to a new server and needed to update several default SSIS parameter values stored in the SSIS Catalog.

My answer here consists of the two steps I believe are required to perform this operation:

1. Migrate the SSIS Catalog artifacts from one SQL Server instance to another; and
2. Override SSIS Catalog Design-Time Default values using SSIS Catalog Configured Literals.

Migrate the SSIS Catalog

SSIS Catalog Compare is designed to support SSIS Catalog migration between on-premises and / or cloud-based SSIS Catalogs. Please note: SSIS Catalog Compare is a for-sale product and there is currently no trial edition that includes migration functionality. SSIS Catalog Browser is free and shares Catalog-viewing code with SSIS Catalog Compare.

To migrate an SSIS Catalog Folder, you can deploy or script. Begin by connecting to the source and target SSIS Catalogs:

Deployment of the folder and its contents is fairly straightforward:

After deployment, the folder and contents are migrated to the target SSIS Catalog:

Scripting an SSIS Catalog folder and its contents is also straightforward:

Select a folder to land the script(s) and ispac file(s):

The script(s) and ispac file(s) are ready to be executed:

SSIS Catalog Compare Enterprise Edition includes the SCCPAC Deployment Wizard which allows the deployment of a “bundle” that includes the scripts and ispac files from an SSIS Catalog folder:

Once executed, the SCCPAC Deployment Wizard (quickly) deploys the SSIS Catalog folder and its contents to the target server:

Deploying and scripting are two ways to migrate SSIS Catalog artifacts using SSIS Catalog Compare.

Update SSIS Catalog Parameter Values

Disclaimers: Test everything you read here (and on any blog) on a non-Production server.
Trust no one. Not even me. As time passes, some or all of the (free) advice I share (freely) here may become outdated.
Back up the SSISDB database on a non-Production server.
Restore the SSISDB database backup on the same non-Production server.
Validate the SSISDB database has been restored properly on the same non-Production server.
Then and only then, proceed.

The first thing you need to know about the SSIS Catalog and SSIS parameters is: The SSIS Catalog treats SSIS Connection Manager properties as parameters. Don’t believe me? Pop into Azure Data Studio, connect to your instance, and execute the following T-SQL query:

Select *
From SSISDB.[catalog].[object_parameters]

The results will be akin to:

Some of the parameter names begin with “CM.”. Those parameters are connection manager properties.

Three Sources of SSIS Catalog Parameter Values

SSIS Catalog parameters have three sources of values:

1. Design-Time Default
2. SSIS Catalog Configured Literal
3. SSIS Catalog Environment Variable (referenced and then mapped)

You can see the three sources in this screenshot of the Set Parameter Value dialog:

In the screenshot above the parameter value is actually a connection manager property – the ConnectionString property – for a connection manager named “Source” in an SSIS package named “Package.dtsx”. This parameter is getting its value from an Environment Variable, configured via a Reference to the SSIS Project.

Learn more about SSIS Catalog Environments, Environment Variables, References, and Reference Mappings at SSIS Catalog Environments– Step 20 of the Stairway to Integration Services.

The environment variable happens to also be named ConnectionString, as shown in the “Use environment variable” dropdown in the Set Parameter Value dialog. This is the third of the three sources of SSIS Catalog parameters listed above.

The first source of SSIS Catalog parameters listed above is Design-Time Default and we see that value represented in the “Use default value from package” textbox – or rather, we would see it if I hadn’t covered it. This value is always present with every project parameter, package parameter, or project / package connection manager property. This value is populated during deployment of the SSIS project or package to the SSIS Catalog.

The second source of SSIS Catalog parameters listed above is SSIS Catalog Configured Literal and we see that value represented in the “Edit value” textbox above. You can type or paste values into the Edit Value textbox and, thereby, manually enter values for parameters.

We will use SSIS Catalog Configured Literal values to update Design-Time Default values… read on…

Overriding Design-Time Default Values

One challenge is overriding design-time default values. One way to accomplish this is to open each SSIS project, change the values manually, and then redeploy each SSIS project. That seems like a bit of a hassle to me.

The SSIS Catalog provides functionality to address this issue: Configured Literals. There are two steps to automating the replacement of design-time parameter values with literal values:

1. Identify the parameters that contain the design-time default values you desire to override; and
2. Update these parameters with literal values.

Identify Target Parameters

One way to identify candidate parameters is to use a T-SQL script. I use a script similar to the following to identify target parameters:

Use SSISDB
go

— start a transaction
begin tran

— declare and initialize Find and Replace criteria
declare @findSearchString nvarchar(1024) = N'%Data Source=vDemo19\Demo%'

— get a list of parameters that meet Find criteria BEFORE the update
Select *
From SSISDB.[catalog].object_parameters
Where Convert(nvarchar(4000), design_default_value) Like @findSearchString
  And Sql_Variant_Property(design_default_value, 'BaseType') = N'nvarchar'
  And default_value Is NULL

Update Target Parameters

The UPDATE portion of the script is as shown here:

— perform the update
Update SSISDB.[catalog].object_parameters
Set default_value = Replace(Convert(nvarchar(4000), design_default_value), @findString , @replaceString)
Where Convert(nvarchar(4000), design_default_value) Like @findSearchString
  And Sql_Variant_Property(design_default_value, 'BaseType') = N'nvarchar'
  And default_value Is NULL

In my script, I want to find and replace the name a SQL Server instance: “vDemo19\Demo” with “.\Demo”. Granted, this is a contrived example since I am executing SSIS stored in the vDemo19\Demo SSIS Catalog. Contrived or not, it is an example…

I wrap the T-SQL statements in a transaction, declare and initialize a few parameters, execute a couple test queries – the first test query searches for the string I wish to find, the second test query searches for the string I wish to replace. The middle section of the script performs the update using the T-SQL REPLACE function shown above. The last section of the script repeats the earlier tests that search for “find” and “replace” values in the object_parameters view.

In its entirety, the script appears as shown here:

Use SSISDB
go

— start a transaction
begin tran

— declare and initialize Find and Replace criteria
declare @findString nvarchar(1024) = N'Data Source=vDemo19\Demo'
declare @replaceString nvarchar(1024) = N'Data Source=.\Demo'
declare @findSearchString nvarchar(1024) = N'%' + @findString + N'%'
declare @replaceSearchString nvarchar(1024) = N'%' + @replaceString + N'%'

— get a list of parameters that meet Find criteria BEFORE the update
Select *
From SSISDB.[catalog].object_parameters
Where Convert(nvarchar(4000), design_default_value) Like @findSearchString
And Sql_Variant_Property(design_default_value, 'BaseType') = N'nvarchar'
And default_value Is NULL

— get a list of parameters that meet Replace criteria BEFORE the update
Select *
From SSISDB.[catalog].object_parameters
Where Convert(nvarchar(4000), design_default_value) Like @replaceSearchString
And Sql_Variant_Property(design_default_value, 'BaseType') = N'nvarchar'
And default_value Is NULL

— perform the update
Update SSISDB.[catalog].object_parameters
Set default_value = Replace(Convert(nvarchar(4000), design_default_value), @findString , @replaceString)
Where Convert(nvarchar(4000), design_default_value) Like @findSearchString
And Sql_Variant_Property(design_default_value, 'BaseType') = N'nvarchar'
And default_value Is NULL

— get a list of parameters that meet Find criteria AFTER the update
Select *
From SSISDB.[catalog].object_parameters
Where Convert(nvarchar(4000), default_value) Like @findSearchString
And Sql_Variant_Property(default_value, 'BaseType') = N'nvarchar'
And default_value Is NULL

— get a list of parameters that meet Replace criteria AFTER the update
Select *
From SSISDB.[catalog].object_parameters
Where Convert(nvarchar(4000), default_value) Like @replaceSearchString
And Sql_Variant_Property(default_value, 'BaseType') = N'nvarchar'
And default_value Is NULL

— before committing, verify Find and Replace criteria meet your expectations AFTER the update
— if so, commit
— if not, rollback and try again

— commit
— rollback

Conclusion

This is one way to leverage built-in SSIS Catalog functionality to facilitate the migration of parameter default values stored in the SSIS Catalog.

Hope this helps!