Change the servername dynamically

[GeertVerhoeven]

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

 

[gradley]

You should add configuration files to each of your packages. I would recommend 1 XML configuration file per database. In each package, you want to save the server name and/or the connection string to the configuration file. Each server you deploy the package to should also have these configuration files. You can modify the XML configuration file to change the server name and connection strings based on the server.

There are several good articles in BOL and the various SSIS blogs to show how to use config files.

Note: If you don't have the luxury of putting the configuration files in the same drive/folder location on all the servers, you can use Indirect Configurations (also in BOL).

Good Luck.