SSIS Package Configuration in SQL Server 2008

After developing SQL Server Integration Service (SSIS) packages, it is highly unlikely that you can deploy the package and execute in the production environment directly. You may have to work in the development environment following Test and UAT environments and later deploying to the Production environment. For all these environments you probably have different servers and folders to work with.

Apart from different environments, sometimes, there are others changes. Sometimes the client may change the drive from C to D or D to E, or change the Database name. If you are using SMTP mail servers, you may have to change the server IP and authentication when the environment changes. So whenever the environment changes, you may have to change all the configuration settings associated with SSIS packages.

You can avoid all the headache of changing these settings by using package configuration.

Here are the steps to setup Package Configuration in SQL Server 2008.
 
1.   Once the SSIS package is developed, Right Click on surface area and select Package Configurations... 
 

2.   It will open Package Configuration Organizer. Check Enable package configurations and click Add... button.
 

3.   It will open Package Configuration Wizard. Click Next to proceed.
 

4.   There are few configuration types available with package configurations. They are: XML configuration file, Environment variable, Registry entry, Parent package variable and SQL Server. Select Configuration Type from dropdown. Write configuration file name with full path in Configuration file name textbox.
 
5.   Suppose we need to select database settings for OLEDB Connection Manager Local.MyDatabase which is the connection manager for the SQL Server database. Then you will need to select the properties you require to save as a package configuration from the following screen.
 

For connection manager, you can either select entire ConnectionString property or you can select ServerName, UserName, Password, and InitialCatalog to construct the connection string. I prefer latter one as it gives more options when there is a change.

6.   Click on Next button followed by Finish button to complete the wizard. Now you can see config.DtsConfig file at the location you mentioned in step 3. Below is snapshot of config file (I did some formatting for better visulation)

7.   That is all you have to do for this example. The next time you load the package, your package will read the configurations from an XML file. You can verify this by changing the XML file contents and reloading the package. After reloading the package, view the connection manager properties and you can see that the connection manager properties are updated from the values in the XML files are updated for the properties.

I personally prefer using Package Variables in config file instead of connection managers because that gives me facility to use in hundreds of packages where Server and Database name are same. however, you need to map the variables in connection manager properties.