ssis xml configurations - configs overwrite packag

Question

i worked all day yesterday trying to back out the impacts of an incorrect connection string in my XML config file. i went through each package, deleting the faulty connection manager and re-adding it with the correct server name. each time i did this i explicitly updated the configuration by removing it, then re-adding it with the overwrite option. but then i made the horrible mistake of pulling up the parent package, which used a different xml config, which updated the connection manager again to the wrong server name .

i pulled the configurations out the parent package and started fixing the child packages again. having fixed the child packages, i called one of them from its now xml config free parent. i was really shocked and disappointed to see that it again had the wrong server in the connection.

How could this happen?

i also noticed through wordpad++ that the config file was updated. my hypothesis is that when the parent's configuration updated the connection manager, SSIS automatically updated the now corrected config file and broke it again.

does SSIS really update config files automatically when components of it change? is it really bidirectional like this? if so its totally confusing behavior - like a ping pong ball, or putting fingers in dykes. i am getting ready to recommend that we never use XML configurations!!.

Answer 1

UPDATE: I was wrong. See my comment below.

So I think you have one (possibly two) fundamental misunderstandings of how configurations in SSIS work.

The standard process for making a connection manager configurable would be:

1. Create connection manager, point it to your local / dev connection in BIDS.
2. Create one configuration for the connection manager's ConnectionString, ServerName, and DatabaseName properties. (If it's a file, add filePath, etc.)
3. In subsequent packages, just create the same connection manager, add a configuration, point it to your existing configuration file, and choose "Reuse Existing ..." (not "Overwrite", that throws out all the work you did in step 2. )
4. Deploy packages out to server and XML file to the same local location on the server.
5. Modify the XML file on the server to point to the proper database connection for that server (dev, staging, production, etc.)
6. When you run the package on that server, it'll use that XML file at run time and use the server's respective databases when running.
7. TOTALLY OPTIONAL: We actually use one XML file per data source and explicitly named connection manager. This means we can "guarantee" via naming conventions of our connection managers that all our packages which need to use a certain database are hitting the exact same database. We store these data sources at the project level and just create New Connection from Data Source, then add the configuration with the matching name, "Reuse Existing", hit OK, and you're all set.

So one misunderstanding you have is deleting the connection manager. A configuration file does not "update" a connection manager. A connection manager's properties are set at design time and are whatever you see when you double-click it in BIDS. These are hardcoded into the package (view the code and see for yourself) and can only be changed in the connection manager editor itself. So there's no such thing as a "faulty" connection manager, and no point in deleting it - as long as when you get a "success" when you test the connection in the editor, that connection manager is good to go.

Your second misunderstanding is how configuration files work. A configuration file merely replaces properties in the package execution with its own values at runtime. It doesn't modify the package at all. And conversely, the SSIS package itself will never modify a configuration file. That can only be done using a text editor outside the BIDS process or via the BIDS Configurations editor - which is I what believe happened.

I can't tell exactly by the kind of general timeline you've provided, but your using the Overwrite option suggests that you basically allowed whichever configuration edit that occurred last to "win" the privilege of setting the value all of your files would use for that particular connection.

Anyway, I would (as you can probably guess) totally recommend using XML Configurations as they are (or were, I thought!) pretty straightforward and in my opinion the easiest deployment option for a multi-tiered SSIS environment.