DTAP package configurations - part 1

Case
I have a DTAP (Development, Test, Acceptance, Production). How do I use SSIS configurations over these multiple environments?

Solutions
The most commonly used solutions for configurating multiple environments are:
a) XML Configuration File
b) SQL Server Configuration (in combination with environment variable)
c) SSIS 2012 (in combination with the new project deployment)

In this post I will eleborate the XML Config file solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.

Simple Scenario

1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.

Enable package configurations

2) Configuration Type
Select XML configuration file (default value) in the Configuration type select box and enter a filepath for your config file. After that click Next.

Configuration Type

3) Select Properties to Export
In this step you select all the properties you want to configure in the config file. In our case we select the ConnectionString property of both Connection Managers. After that click next.

Select both ConnectionString properties

4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations. After that hit the Finish button and close the configuration window.

Configuration name

5) View the file
If you browse to your config file and open it in for example Internet Explorer then you can see what's in it. Now you can just copy the xml config file to all environments. The path to the config file is embedded in the packages. So if you keep the file path the same, then you can just copy the packages and config files between environments. You can edit the config file in notepad to change the values for each environment.

6) Deployment Manifest
If you use different paths to store the config files then you should use the Deployment Manifest to change the embedded file path of the config file. Go to the properties of your project and go to the Deployment Utility page and select True at the CreateDeploymentUtility.

CreateDeploymentUtility

7) Build to create the Deployment Utility
Build your project (right click project in Solution Explorer and select Build). This will create the 3 files in the bin folder of your project:


Folder: \ProjectName\bin\Deployment\

8) Deployment
Now you can give these three files to the server administrator and let him/her double click on PackageConfig.SSISDeploymentManifest to deploy the package(s) and determine the config file folder. It's even possible to change the config value while deploying.