synchronize connection strings

In SSIS you have the possibility to create datasources in your project. These are design time projectlevel connectionstrings to connect to your datasources. So this seems a good idea: define once and reuse this multiple times in your packages. So when I start a project I create datasources and in the connection manager box (below) I select “New Connection from Data source”. Great! Perfect! This is what I want. I create the datasources at project level and use them in the packages whenever I need them. Perfect!

But troubles begin when you start using package configurations. Mostly i create package configurations (dtsConfig files) and use the environment variable for indirect use. When you start changing the package configurations files by hand you will receive an error when opening the specific package who uses this configuration: “Synchronize connection strings” (Grrr). The old connection string is the one changed in the file and the new one is my old connection string. Awkward! The other way around seems more logic to me.

Some info from BOL:

“When you add a data source to a package by using SSIS Designer, SQL Server Integration Services adds a connection manager that can be resolved as an OLE DB connection at run time, sets the connection manager properties, and adds the connection manager to Connections collection of the package. To associate the data source and the connection manager, Integration Services sets a property, DataSourceID. Because a data source is not created within the context of a package, multiple packages can use the same data source”

I searched the internet for a solution for this but I can’t find nothing better than: “Don’t use datasources”. Great! This is not the answer i want but i didn’t find any better solution so here is my best practice:

1. The capability of using datasources throughout your project is very handy. So at first you could create and use them until your project reaches a maturity level. After this delete the datasources.

2. Create different sets of configurations for Development, Test, Acceptance and production.

3. Use batch files for creating the environment variables (SETX XXXX_Config D:\XXXX\Configurations\XXXXXX.dtsConfig /M) for development, test, acceptance and production (DTAP).

Greetz,
Hennie