Dynamically connect to the server a package is deployed on

Question

I have a package that needs to be run in different environments (different server\instance, same database name) for example:

server: live-db01\live db: Campaign

server: dev-db01\livedebug db: Campaign

The package itself is identical for each environment. The only thing that changes is the connection string for the server. These packages are sent to appropriate department and they deploy it onto their server.

My question is let's say the package is installed onto the live-db01 Integration Services instance. Is there a way to access this server information inside the package and set the connection string accordingly?

Right now we deploy the same package with 4 different configurations with the only difference in the XML .config is the connection string. Is there a way to deploy a single package without the config that dynamically changes its connection string based on the server it is deployed in?

Answer 1

If you set the value of your connection string using a variable you can use a script task to set the variable to whatever you want.

Because script tasks have access to the .net base classes you can do a check against System.Environment.MachineName to get the host and set the config accordingly.

If you had a string variable called "CONNECTION_STRING" and added a script task that did something like:

 string hostName = System.Environment.MachineName;
        string connectionString = "";
        switch (hostName)
        {
            case "host1":
                connectionString = "SERVER=abc;Initial Catalog=blah;...";
                break;

            case "host2":
                connectionString = "SERVER=abc;Initial Catalog=blah;...";
                break;

            case "host3":
                connectionString = "SERVER=abc;Initial Catalog=blah;...";
                break;


        }
        Dts.Variables["CONNECTION_STRING"].Value = connectionString;

That should do what you want. To use the expression go to the Expressions property on the connection and override the "ConnectionString" property with your variable "@[User::CONNECTION_STRING]" and you might want to enable delay validation for the connection.