I am using SSIS in SQL Server 2008 R2 and have been creating packages for a few years, but have not done so where the packages will be going from a development, to a QA and finally to a production environment. The database name will be the same in each environment, but the server name will differ. Also, the SSIS packages will reside on the same server as the database. I will eventually be applying Microsoft Foundation Services as the source control.
My best guess for keeping everything the same while applying a different server name is to add the Connection String to Expressions for the OLE DB and use the default system variable MachineName in place of the hardcoded value. This would need to be applied twice in the connection string where the server name is defined.
(e.g. "Data Source = " + @[System::MachineName] + ";Initiall...."
Is this the best approach or is there a mechanism in Microsoft Foundation Services that will handle this automatically?
I'm not sure what you mean by "Microsoft Foundation Services" - do you mean "Team Foundation Server" perhaps? If so, it will not look after your configuration requirements.
The standard way to do this is by using 'connections' in your SSIS package for your various sources and destinations.
The connection string properties for these connections can easily be overriden at runtime (to deal with exactly the situation you mention) through the use of Package Configurations (see here
While your requirement can probably be accommodated the way you mention, anything more sophisticated is likely to cause a headache. It will take a little longer to set up, but having a separate config database per environment, which includes all your config settings for all your packages, keeps things visible and allows you to tweak almost any package property you can think of.
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.