I have had this conversation with quite a few people, including a recent conversation with http://twitter.com/joechr309, and decided to blog about the choices that I've made regarding configuration files and sql server. I happen to love the idea of de-coupling a DTS package from the sql server. I can now completely isolate sql server from external programs. If I happen to have a very non-sql intensive SSIS process (importing various files, moving them, manipulating them, ftp'ing them, etc) I no longer have to consume sql resources (granted I need to do lookups and other minor sql tasks, which is why it is an SSIS package). I can also use SSIS high availability functionality. Continuing on this path of separating SQL Server from SSIS, why not have a configuration file to make the necessary changes? Why bind the SSIS package with the need to go to a specific sql server to extract run-time variables, when I can simply provide a configfile with all of that information. I keep both the configuration file and the SSIS package on a secure fileshare (with limited access) and I have met SOX compliance and de-coupled my sql servers.
Having tables contain run-time information can get messy. Do I put the configuration for multiple SSIS packages on the same server and then if I do, where do I put them? Does the development team mandate that every sql server will have a database named 'configurations' and within that one or more tables that the SSIS packages will look for their specific information? How do I efficiently store this? Do I store it by unique SSIS package ID or by another convention? I am sure there are many answers to these many questions, but if you look at the simple solution, it would be a configuration file. I don't need to grant developers access to databases to configure their packages, they can simply edit the configfile on their own. They no longer need a database change request to make configfile modifications. These are a few reasons why I love the idea of using configuration files over other methods. I have tried to see the downside to it and I am looking for those who taken the database approach to comment.