SSIS packages, configuration settings and multiple servers

When I started building SSIS packages I frequently ran into the issue of having to maintain configuration files across different environments. The way I handled it was to use SQL server as the storage place for my package configuration. With that comes the need to be able to point to the appropriate SQL server and database depending on the environment or server. This is where the environment variable comes in handy. With that being said here are the steps to create a package and associated elements to accomplish a flexible configuration setup across servers.

  1. Setup the configuration database. The configuration table can reside in an existing database or in a new one. For this exercise let’s create a new database called “ConfigDB”.
  2. Create an environment variable “SSIS_CONFIG_DB” and set the value to the ConfigDB database. For example, Data Source=MySQLServer;Initial Catalog=ConfigDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False; 
  3. Set the connection string to the ConfigDB from the environment variable.
    1. Create a new package.
    2. Create a new variable called “EnvName” as a string. You can set the value to “Development”.
    3. Create a new OLE DB connection and set the connection to the ConfigDB database.
    4. Open the Package Configurations window.
    5. Check the Enable package configurations.
    6. Click the “Add…” button to start the Package Configuration Wizard.
    7. Choose “Environment variable” and then select the “SSIS_CONFIG_DB” variable from the dropdown.
    8. For the target property, select the ConnectionString for the above created connection manager and then click Next.
    9. Assign a name for the configuration and finish.
  1. Generate and save configuration settings.
    1. Open the Package Configurations window.
    2. Click the “Add…” button to start the Package Configuration Wizard.
    3. Choose “SQL Server” in the Configuration Type.
    4. Select the database connection to the ConfigDB and choose the default configuration table name “SSIS Configurations”.
    5. Enter “MySQLConfig” in the Configuration Filter. This could be any meaningful name.
    6. Click the Next button.
    7. In the objects locate the variable “EnvName” and check the Value option.
    8. Click Next, and then give a name to the configuration. For example, “Init_Settings”.
    9. Click Finish to generate the configuration settings and then close.
    10. To verify that the settings have been created, look at the table rows for “SSIS Configurations” in the ConfigDB database. There should be a row with the ConfigurationFilter “MySQLConfig”.
  1. To verify the working of this setup create a Script task to display the value of the variable EnvName. Make changes to the variable in the ConfigDB database and will see it reflected in the package execution.
  2. Moving to another server. To have this package run in another server with different EnvName values follows the steps below.
    1. Generate the SQL script for database and table from the original ConfigDB and recreate it on the new server.
    2. Copy  the row from the original configuration table “SSIS Configurations” and paste it in the new table and modify the ConfiguredValue for the variable EnvName.
    3. Setup the environment variable “SSIS_CONFIG_DB” with the value set to the connection string to the new configuration database. For example,  Data Source=MyNewSQLServer;Initial Catalog=ConfigDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False; 
    4. Copy the SSIS package to the new server and run it. You should see the value from the new server displayed.

 

 

I’ve used this method to move my packages from development to QA to production with minimum to no changes to configuration settings. I also use this to turn on and off logging modes in the different environment to debug packages.

Advertisements

2 thoughts on “SSIS packages, configuration settings and multiple servers

  1. Milton says:

    Mario,

    Could you suggest a best practice of having one configuration table that serves to ~30 packages. Or do I have to set different configuration filters for every package?

    Regards,
    Milton

    • Mario says:

      Milton,

      With the described setup there will be one table “SSIS Configurations” that will store all the configurations for multiple packages. The column [ConfigurationFilter] is used to group the settings as required. If you have settings that are common across multiple packages, you can design the packages use the same configuration filter. I have about 15 SSIS packages storing their settings in the table “SSIS Configurations”. In some cases packages share settings.

      I hope this addresses your question.

      Mario

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s