Maintaining a central repository for all connection information for SSIS packages is important especially if you have multiple packages that connects to one or more different systems. Keeping track and performing changes on the connections will be hard if not done properly.

This blog entry will describe on how to use SQL Configuration Type to store the connection info in combination with the traditional File ConfigurationType (dtsConfig) to store package specific configurations.

1. Setting up the configuration store

A table with definition below needs to be created on an new/existing database, this will store the connection information.

SSIS Configurations
Column Name	Type	Description
ConfigurationFilter	Nvarchar(255)	This field is used by SSIS to indentify a set of property/values pairs that are part of the same configuration entry in the Package Configurations Organizer.
ConfiguredValue	Nvarchar(255)	It stores the value that is used to update the package property specified in PackagePath column
PackagePath	Nvarchar(255)	The path that point to the property being configured
ConfiguredValueType	Nvarchar(255)	the SSIS data type of the property being configured

2. Environment Variable

In this step, we would create an environment variable which will store the connectionString information to the configuration store (see step1).

1. On the Start menu, click Control Panel.

2. In Control Panel, double-click System.

3. In the System Properties dialog box, click the Advanced tab, and then click Environment Variables.

Figure 1 – System Properties

4. In the Environment Variables dialog box, in System variables frame, click New.

In the New System Variables dialog box, type SSISConfiguration in the Variable name box, and Data Source={DatabaseServer};Initial Catalog={DatabaseName};Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False; in the Variable value box.

5. Click OK.

6. Click OK to exit the System Properties dialog box.

3. SSIS Package Settings

It is assumed that the package is already in the final phase of development and the only thing needs to be done is to update it to retrieve settings from the configuration store and use it during runtime.

 3.1 Add Configuration Store connection

Since we are going to retrieve connection settings from configuration store that is a table we need to define a new OLE DB connection that does that.

1. In the Connection Managers, right click New OLE DB Connection.

2. Click New, Set the valid credentials to connect then on the Connect to a database server(this is the connection to cofiguration store) , Select <{Database}>.

3. In the Connection Managers rename the added connection to OLEDB_SSISConfiguration.

 3.2 Configure Configuration Store Connection

In the following steps we are going update the connection to configuration store to use the environment variable we defined step (3.1)

1. Click SSIS Menu, Package Configurations. Package Configurations Organizer opens.

2. Click Enable package configurations.

3. Click Add, Click Next.

4. In the Configuration Type, Select Environment variable.

In the Environment variable drop-down, Select SSISConfiguration.

Click Next.

Note:

If the SSISConfiguration is not on the list try re-opening the BIDS (Business Intelligence Development Studio)

5. In the Select Property Page in the Connection Managers,

Click OLEDB_SSISConfiguration,

Click Properties, Select ConnectionString.

Click Next.

6. Type OLEDB_SSISConfiguration in the Configuration name and click Finish.

7. It should look like this:

3.3 Configure existing connection to use configuration store.

 After the connection to configuration store is properly setup the next step is to update existing connections in the package to retrieve settings from it.

Important:

Repeat the steps per connectionin the Package. If the package has connection to 2 database, the steps below needs to be repeated 2x.Example below illustrates an SSIS package that has connection to a database: SampleDB.

1. Click SSIS Menu, Package Configurations.

Package Configurations Organizer opens.

2. Click Add, Click Next.

3. In the configuration Type, Select SQL Server.

In the Connection, Select OLEDB_SSISConfiguration

In the Configuration Table, Select [dbo].[SSISConfigurations]

In the Configuration Filter, Set it to the <name of the connection>,

Ex. OLEDB_SampleDB.

Click Next.

4. In the Connection Managers, Select the connection (name of collection in step 3), Properties, Select ConnectionString.

Click Next.

Important:

Select only one ConnectionString in this step since the entire step will be repeated per connection. This makes sure that the settings defined in configuration store can be reused in other packages.

5. In the Configuration Name, type the name of connection (in step 3). Click Finished.

6. Repeat steps for other connections (if applicable).

7. Update the SSISConfiguration Table if SQL Login is used since SSIS doesn’t save sensitive information such as password.

Note:

Set the connection manager to DelayValidation=TRUE if during the execution the validation fails. For some reason, it uses the value in the package and not the values from the SQL Configuration store (Database).

If you encountered this error:“Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)   Could not complete wizard actions.” It means that one of the properties exceed the length limit of the SSIS configuration table, normally it’s the connection string that causes the problem. Try to shortened the connection string by truncating the Application Name property or try to giving it a short name.