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.

Solution 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

Solution 2

We use a configuration table rather than a file. You start with an environment variable to point to the database with the config table and then pull all other configurations from there.

Here's a sample of a script to populate a configuration table (you would create a separate script for dev server, QA Server, Prod server:

--connections
--for MyDatabaseName
INSERT INTO MyDatabase.dbo.SSISConfig VALUES (
'MyConfigurationName', 'Data Source=MYSQLSERVER;Initial Catalog=MyDatabaseName;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;', 'Package.Connections[MyDatabaseName].Properties[ConnectionString]' , 'String' )

--variables
--FilePathImportServer
INSERT INTO MyDatabase.dbo.SSISConfig VALUES (
'MyConfigurationName', '\\MYSERVER\SOMECLIENT\Import\', '\Package.Variables[User::FilePathImportServer].Properties[Value]' , 'String' )

--select * from SSISConfig where configurationFilter = MyConfigurationName

Solution 3

I populate a variable with the servername from a query that has the active server name. then in a script task I just change the connection string

This example is for an Excelfile but it would be very similar for a sql connection

    Dts.Connections("ExcelConnection").ConnectionString = 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\" +

 Dts.Variables("ActiveServerName").Value.ToString + "\Summary_" + 

Dts.Variables("FileDate").Value.ToString + 
".xls;Extended Properties=Excel 8.0;HDR=YES;"

Solution 4

In addition to the config file...

You can define a variable which can be set by the command line or in a batch job or SQL Server Agent job. The variable can be substituted into the server name (by an expression, IIRC).

This is useful for if you want a single file deployment.

Edit:

Example: (Sorry, SQL Server Central requires registration)

Solution 5

I have found that the config file is a pretty handy way to go about this.

If you schedule your package to execute from SQL Server Agent, you can specify the config file to use from a file path, and that usually takes care of all my parameter requirements.

If you want to keep the results in SQL Server, you can store them in a table, and retrieve them. But now you have to know which server / table to go against for Dev/Test/Prod, and you are back to the original problem.

My advice: have a config file in the same physical location on all servers (Dev/Test/Prod) and have the job point to the appropriate config file for the parameters.

View more solutions