Shared Data Sources vs. OLE DB Connections In SSIS

I've been using Shared Data Sources in all of my SSIS projects because I thought it was a "best practice". However, now that everything is under source control (TFS) just about every time I open a package it updates the Data Source connection in the package. I either have to roll the change back or check it in with some nonsense description.

I saw this SSIS Best Practice blog entry and it got me thinking about whether Shared Data Sources are really the way to go.

Don’t use Data Sources: No, I don't mean data source components. I mean the .ds files that you can add to your SSIS projects in Visual Studio in the "Data Sources" node that is there in every SSIS project you create. Remember that Data Sources are not a feature of SSIS - they are a feature of Visual Studio, and this is a significant difference. Instead, use package configurations to store the connection string for the connection managers in your packages. This will be the best ro开发者_开发百科ad forward for a smooth deployment story, whereas using Data Sources is a dead-end road. To nowhere.

What are your experiences with data sources, configuration and source control?

We use SVN, so it doesn't integrate in the same way the TFS does. When starting out with SSIS, I used Shared Datasource, but it got me into all sorts of trouble when I finally uploaded the package to run on a schedule. So now I use XML configuration files (package configurations) to provide the connection properties, and I've never had any trouble with these.

So I agree, share datasources = bad idea/lack of hair

when we were migrating from SSIS 2005 to 2008, data sources were quite painful. Configurations on the other hand are pretty flexible. Especially if you store configurations in one database table - that way you can easily change connections with just one UPDATE statement!