SSIS: Configuration

Nearly everything in the package can be configured.

The main items you will want to configure are the passwords in server connections along with the name of the server and possible the initial catalog (database).
To set up a configuration option right click the package main window and select package configurations.
The options available are to use an XML file, Registry Entry, Parent Package Variable or a SQL database.
In this example I have a database called dataimports which I am going to use. In dataimports I have created a schema for each batch of work I use. This means I can separate out all the SSIS logging and configuration and I can give users access through the schemas.

Once you select the SQL server option you will need to select a database connection to use and then a table. If you already have config tables the drop down box will list them – else you can create a new one. You will need to type in a configuration filter. This is a key word that will match up the configuration settings in the table with this package. The name should be easily identifiably like >> DataDownload.
When you click next you will see a list of configurable items. If this is a new package there will not be very many.

You should try to restrict the configured values to the ones really needed else you will make reading the configuration table difficult which can lead to errors.

A config file should be used to store secure data – like passwords. It should also be used to store values which will or could change. For example a package which emails a user upon completion could have the To field of the email task configured. This way if you need to change the recipient you can change the config table. If a server name is going to change as the package is deployed then store the server name in a config file. You shouldn’t store static or trivial data like the protection level of a database connection – this will probably never change; or the connection string to a database and then all the individual elements of the connection. If you are using Windows Authentication you may not need to store any connection data.

What it looks like in the table:

I have created a primary key on ConfigurationFeature and PackagePath – the table is created without any primary key.

Note for passwords the value stored in the config file will be *****. This is a place holder. You will need to change this value to the actual password and set security on the table to stop people reading it.