I am trying to understand the point of configuration files but I am still failing to see the added benefit other than for file paths. A very common scenerios in the business world is developing SSIS packages on a staging environment and then deploying them to a production environment. In this case the database servers are different. Instead of trying to modify all my package connection to point to the new database I figured I could link the connections in each package to a configuration file. The configuartion file would have two connections strings, one for staging and one for production. Based on the name I could I could load the proper string for the environment. This way I would only have to change one variable for all the packages from DEV to PRD and it would get all the new connection strings. If that was confusing maybe this will help. PACKAGE A - CONNECTION A - CONNECTION B PACKAGE B - CONNECTION A - CONNECTION C CONFIGURATION FILE A CONFIGURATION FILE B CONFIGURATION FILE C In the above layout you can see that I have two packages. Both packages share a connection to database A and have distinct secondary connections. Each connection has one configuration file that contains a connection string for development and production, i.e. (DEV_A and PRD_A). Each package has a user variable called ENVIRONMENT that was either DEV or PRD. Based on this user variable I want to load the appropriate connection strings for each database from the configuration files. This way when I move the packages to production I toggle the one environment variable and it will set all the connections. Eventually I will move this from a user varaible so I could set the property for ALL packages instead of one at a time, but lets not get greedy yet. Anyone have any suggestions on how to proceed with this setup. It sounds very logical to an everyday business so I would think it would be plausible.

What i do is i have minimum 4 XML SSIS Config files (or you can have them in a different layout in a table) 1- For emails (SMTP , TO , FROM , Body message, other settings .....) 2- Source ( if its an excel for example : connection string, tab names, connection .....) 3- Destination ( if its an table for example : connection string, table name , SQL name , db name .....) 4- Folders ( package main root location, backup folder , error folder, etc.......) I am dealling with farms of SSIS each DB has over 300 packages and i dont have any problem with my setting what you have to do is 1- orgenize your plan 2- KNow the customers information , LIKE package folder , SQL server name, DB name and etc........ 3- make the XML config files , you ONLY need to do this once 4- do your deployment set the package and the XML to the customer with his specification that they had gaven you 5- IF the package needs to be changed (modified) you dont need to change the XML 6-and while doing the DEV you dont need to use and configuration, so you dont need to switch from DEV to PROD all you need to do is to set the variablke in your SSIS to your DEV , when deploying leave it as it is because the XML file tthat are set according to the customers specification will overwrite the dev within SSIS variable, it will over take Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Not sure if I understood you completely, but here is what I think you should do: Continue using the Environmental Variable to point your all packages to [one or more] config file(s). (naturally such a config file contains the Dev or Prod settings depends on what server it is). This way you never change anything in the packages, and you keep re-using the very variable obviously the other approach could be setting this Env. var. to a different path, but this is up to you. A link worth to visit: http://sqlblog.com/blogs/eric_johnson/archive/2009/09/21/using-ssis-package-configurations.aspxArthur My Blog

create a seperate configuration file for production and development , so that you no need to modify the configuration file.Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

If you'd like to better understand configurations, check out this upcoming webinar on it: Webinar signup There's also another webinar in our archives that covers deploying SSIS packages in various environments that could be useful to you as well: Webinar link Jorge Segarra SQLChicken.com || Follow me on Twitter! || SQL University Please click the Mark as Answer button if a post solves your problem!

I actually just finished implementing a design like this with the packages. I have an EMAIL and DATABASE configuration file. Both files are stored in a network share. The EMAIL configuration is the same regardless of environment but the DATABASE one contains the environment flag which is in each package. This will tell the package what connection string to use for each database. Seems to be working quite well. I deemed that it would not be a good idea to completely automate the environment flag because I would like to be able to toggle it in the package regardless of the environment. I just have to remember to set it before deploying to production!