Script to update server name in SSIS package

We are doing SQL server migration from 2012 to 2016. One of the challenges was how to update server name in SSIS package programatically without the manual process of opening each package in SSMS =>. configure => connection strings and change them manually.

Use following update SQL scripts:

update [SSISDBName].[internal].[object_parameters]
set design_default_value = ‘NewServername’
where design_default_value in (‘OldServerName1′,’OldServerName2’)
update [SSISDBName].[internal].[object_parameters]
set default_value = ‘NewServername’
where default_value in (‘OldServerName1′,’OldServerName2’)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s