Best way to manage SQL Server Package Configuration data
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. -Albert Einstein
May 20th, 2011 8:54pm

I would rather mention my personal experiences here . For the first one 1) I was able to use a single XML configuration file for multiple packages. The single configuration file can store the global as well as local variables or properties (which are specific to a package). So, I am little apprehended on the overhead of usage of two configurations for a single package. 2) As said in point 1 , I used a single configuration file for all the packages and stored it in a file system. Then , I stored the path for this configuration file in an envrionemnt varaible. The envrionemnt varaible was created during the deployment from DEV to TEST to Production. So , I was able to just change the path of the configuration file from one env to other env. 3) For the third, as per 1 and 2 points, I did not require the connection for configuration table. Happy to help! Thanks. Regards and good Wishes, Deepak.
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2011 3:21am

I would rather mention my personal experiences here . For the first one 1) I was able to use a single XML configuration file for multiple packages. The single configuration file can store the global as well as local variables or properties (which are specific to a package). So, I am little apprehended on the overhead of usage of two configurations for a single package. 2) As said in point 1 , I used a single configuration file for all the packages and stored it in a file system. Then , I stored the path for this configuration file in an envrionemnt varaible. The envrionemnt varaible was created during the deployment from DEV to TEST to Production. So , I was able to just change the path of the configuration file from one env to other env. 3) For the third, as per 1 and 2 points, I did not require the connection for configuration table. Happy to help! Thanks. Regards and good Wishes, Deepak.
May 21st, 2011 3:21am

I agree with deepak point.Thanks Ayyappan Thangaraj UG Lead, Puducherry, http://SQLServerRider.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2011 11:33am

Thanks for the feedback, but I am looking into SQL Server Package Configurations. I do not want to use configuration files except to simply hold the connection info to get to the Package Configuration database table.Make everything as simple as possible, but not simpler. -Albert Einstein
May 21st, 2011 7:17pm

Thanks for the feedback, but I am looking into SQL Server Package Configurations. I do not want to use configuration files except to simply hold the connection info to get to the Package Configuration database table.Make everything as simple as possible, but not simpler. -Albert Einstein
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2011 7:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics