I inherited approximately 150 SSIS packages doing various ETL operations. They use a custom table that requires that we add a new column when a package requires a new type of config data storage.
As part of an upgrade from 2005 to 2008 and a partial rewrite effort I am exploring, among other changes, switching from storing config data in the flat/wide table I mentioned to using SQL Server Package Configurations to manage configuration
data. What do you think of the following?
- Having two Package Configurations per SSIS package. Both will be pointed to the same database containing only the config table, but with different filters. One filter will be specific to the package that will contain package-specific configuration data
and one will be called "global" which will contain configuration data that all packages share (things like "default alert email" should Armageddon ensue during package execution).
- How best to manage configuration data when moving a brand new package from DEV to STAGING to PRODUCTION?
- How best to manage properties for the connection object used to initially connect to the Package Configuration database/table?
Make everything as simple as possible, but not simpler.
View Complete Post