SSIS Package: Need to update the server name and database name

1
votes

I have 100 SSIS package all the packages are created in Development environment.

I need to update the server name and database name of all the packages.

It consumes lot of time updating the server name and database name manually.

Is there a way i can update the database and server name in all the packages?

All the packages are stored at same location.

3
sql-serverssis
I assume you are referring to the Connection Manager server and database names? Do you have configuration files to allow you to change this data depending on environment? - shree.pat18
Yes i am referring to connection Manager Server and database name. What are configuration files? I am pretty new to SSIS. Can you help me in understanding configuration files and How I can incorporate the configuration files in my problem case - Mohit

3 Answers

0
votes

Ideally, you could have a single Master package calling each of your 100 packages which I assume are performing specific tasks. In this Master package, you can pass the Connection Manager details to each 'Child' package during run time itself. The way this is done is by creating variables in each package to hold the server name and database name, and then to update the ConnectionString property in each child package at the start of execution. That way, you only need to generate 1 configuration file for the Master package, and you can configure the database connection parameters as you want. However, this will entail extra development effort as follows:

  • A new SSIS package to act at Framework level and co-ordinate running the 100 packages
  • Variables in the new and existing packages to hold server name and database name
  • A new configuration file in the Master package

The alternative is to just generate configuration files for each of the 100 packages. Then, you will have a separate file for each package, but you will need to make your replacements in each of the configuration files.

You can check out the below links for resources on this:

0
votes

Have you looked into the "convert to project connection"? If you havent done this yet you are in for a bit of a grind to set it up the first time, but after that you can set your connection for the entire project in 1 place, and configure them in 1 go as well. Just right click on the connection and select the "convert to project connection" for the first connection. Then this project connection becomes visibile for all other components in the project and you can set it up.

Further, if you feel brave and smart you can try to find/replace these settings in the xml code of the packages. This is not easy and not a good idea if you dont know what your doing. If you do know what its all about you can complete this job in 10 minutes flat ;)

Dont say I didnt warn you!

-1
votes

Use update scripts to update internal.object_parameters table

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’)

Please find more details on https://moredvikas.wordpress.com/2016/11/17/script-to-update-server-name-in-ssis-package/