How to make the server name dynamic in an SSIS package

Question

I have many SSIS packages that all connect to the same database. I don't want to hard code the server name into the db connection in each database, because it is different from the dev to live server. And those server names might change over time.

I know that you can put it in a config file, but all these SSIS packages are stored in SQL Server 2008 and I want to keep all the details in SQL Server.

Does anyone have any suggestions or advice?

I was thinking about creating a package called GetServerName that returns the server name, which I could use to change the connections in each package. But there might be a better way.

Answer 1

A very nice feature in SSIS to solve just your sort of problem is called package configurations. Many people have different opinions and/or ideas on how to go about storing you dynamic server/package properties/variable values-configuration files, environment variables, sql server, or registry entries. I personally like storing all the information in SQL Server, which is what you are wanting to do. To achieve this, follow the steps in this article: Package Configuration Steps