2

We have a few 100's of SSIS packages in our environment. Last night we moved our database from one server to another and renamed it. We now need to go into all of packages and change the connection manager to point to the new database server name.

We want to know if there is a way to create a @variablename that we can store our new server name in it (i.e. 'ELITEDB.SIRS-db') in it so we can then put the @variablename into the Connection Manager area. This way if we move servers again in another year we only need to make this change once because the new @variablename will be able to pull the new Server name.DBname.

I hope this makes sense to the masses out there?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Chris White
  • 21
  • 1
  • 2
  • How do you execute the packages? – Rednaxel May 17 '13 at 16:13
  • 3
    possible duplicate of [How to parameterize database connection string in an SSIS package?](http://stackoverflow.com/questions/2451758/how-to-parameterize-database-connection-string-in-an-ssis-package) This is the solution that we use (store all configuration parameters in a database table), so that if your server name changes, you just `UPDATE` the configuration table and you're done. – Pondlife May 17 '13 at 16:28
  • If the packages are stored as files, you can just find/replace the hostname O.o – tommy_o May 17 '13 at 21:38
  • Possible duplicate of [SSIS how to set connection string dynamically from a config file](http://stackoverflow.com/questions/2497875/ssis-how-to-set-connection-string-dynamically-from-a-config-file) – Michael Freidgeim Nov 15 '16 at 19:24

1 Answers1

2

If you're working with SSIS 2012, just right-click on the connection and select "Parametrize". Thereafter the connection will be settable with the parameters you create.

Metaphor
  • 6,157
  • 10
  • 54
  • 77