Hi all, I need some deployment advice please. We have approx 50 packages which are being deployed to a UAT server, all the packages reference 3 possible shared data sources, some packages reference all 3 but some only 2, because of this I want to avoid the “connection can’t be found error” problem. I think having a config table on the server is the best approach but am not exactly sure what properties to have in it and whether or not its best to have some kind of variable within the packages or not. Many thanks in advance for your help!

Hi rockyboy1, I suggest having a SQL Server-side (database based) config rather than a file. Since you have so many packages, you can easily migrate from a machine/server to machine/server by employing an Environmental Variable. I will give you a link (below) that best describes the idea: http://www.mssqltips.com/tip.asp?tip=1405Arthur My Blog

here's a shameless self-promotion: http://toddchitt.wordpress.com/2008/06/27/ssis_config/ In it you can see how to set up a Configuration database, create entries in it for the Connection String property of a Connection Manager, then re-use that entry for other packages that have that same Conneciton Manager. Then you will see how to copy that entire database over to another environment, and edite the records in the table accordingly. The end result is a system where you can deploy your packages to multiple environments and be assured that they will always connect to the proper resources. In other words, a package running in TEST environment connects to the TEST database resources. And all with no Package edits in between deploys. Post back here if you have questions.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi many thanks for your replies! Todd I have read your blog and that was exactly the approach I have been thinking of doing but I'm just a little bit confused as to what exactly the benefits are as opposed to simply storing the connection string to each of the 3 shared data sources in an xml file. The reason I ask is because with the database approach I would have to set up 2 or 3 config for each package which means doing it approx 120 times for 50 packages, although some packages use 2 and others 3 shared data sources there would be no harm in me just adding the extra datasource where its missing in the packages even though it would be used and then the xml config wouldn't complain about not being able to find a datasource. Please let me know if I have missed something fundamental! Thanks again!

I'm a bit of a purist. So if a package only needs two Connection Managers, then that's all that it will have. Extraneous stuff isn't there. I'm also a database guy, so given the option to store Config stuff in a database or an XML file, I'll choose the former every time. Let's suppose you go with three Connection String entires all defined in ONE XML File. What happens when you take on your NEXT project, and you decide that this series of package needs to Configure a Variable (like maybe a shared folder where it will fine the input CSV files) and you are working with two different connections. Do THOSE config entry then also get added to the first, or do you create another XML file. Next project has element of BOTH, what now? Here's what I do: Start a project with a bunch of Shared Data Sources. Then I create ONE package an set up all the framework stuff like Logging, Connection Managers, Configurations, Check-point files, etc. Then when I need to create a package in the project, I use that one as a template and all that 'stuff' is already there. I really suggest you take the long route on this. Yes, it means more work up front, but I think your packages will be the better for it. And you'll gain a good understanding of Configurations in the process.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks Todd, I have in fact amended all 50 packages and used the Database Config approach, took a while but hopefully should be worth it in the long run! Thanks for your help!

Hi Todd, I was just wondering if I deploy the packages using the manifest from my machine (not copying it to the teste server) and point to the Test server is there a way of also specifying the SSIS_Confog file path to also point to the test server as currently when I deploy the packages from my machine it looks like the packages created on the test server still refernece the SSI_Config file on my machine. Hope that makes sense. Many thanks!

Deployment, oneof the sticy issues with SSIS. Do this (and thank mae later): Download and install BIDSHelper from CodePlex.com. Free utility GREAT! allows you to do one-clicke deployment of a single package after setting Project Configuration properties. Kind of lid SSRS PRojects have.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi Todd, thanks for this, I will give it a try. I have query though regarding your approach, I did implement it however the problem we had was everytime we opened visual studio we would get the 'out of synch' message pop up for all 100 packages and obviously this caused us issues, so I ended up changing all the data sources for connection managers and ensured they were called exactly the same in each package and still used the same table config approach, can you any problems with this approach ro is there a way of avioding the out of synch message? Many thanks!

I use Shared Data Sourced for all my projects. Make sure the Connection String in the Config table EXACTLY matches the connection string of the Shared Data SOurce. Even down to the order in which the various elements of the string are presented. I usually just copy and paste the string from the SSIS package to the Config table to eliminate this issue. But also be aware that it could crop up if someone edits the Shared Data Source after the Config table has been populated. Best of luck!Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks i will give that a try but it does seems a little bit inconsistant the way it updated datasources, having simply used connection managers instaed and making sure they are always named the same, I can't quite see that many benefits of using the data sources as the connection managers still use the config approach but I suppose there is a slight chance of misspelling one of the connection managers but if using a template that should be avioded. Am I missing an obvious drawback? Many thanks