Changing Config Table Location On The Fly

Hi everyone,


I am trying to create a proof of concept to show that we can have packages deployed on 3 different servers and all we need to do is tell the package, upon execution/scheduled task, where to go fetch it's configurations from.

The configurations are using SQL Server as the package configuration. This makes it easier for DBA's to maintain since DBA's are responsible for package executions and job scheduling.

For example, the configuration database and all package configurations would be found on server1, server2 and server3 The difference in values is that on Server1, the configuration for the source data and the destination data point to Server1DatabaseSource and Server1DatabaseDestination and on server2, the configs point the source to Server2DatabaseSource and Server2DatabaseDestination and for server 3, the same thing but pointing to server3.

There is also a connection for the SSIS_Config database we're getting the configurations from. In theory, if we specify a different server where this SSIS_Config database is found, it should override the settings in the package. No?

When I schedule OR execute the package, it's always getting it's configs from the config specified in the package independent of wether I specify it in the Connection Managers of the Execute Package Utility when I manually execute it OR in the data sources tab when I configure the package to be run as a job in SQL Server 2005.

Am I missing something here?


Thanks,

Rob