Pretty sure there is a trick to this I'm missing. Would appreciate any light you can shed.


Actually, it's quite easy....you can create a configuration file (which is also an XML file). The default extension for an SSIS package config file is .DTSCONFIG.

So in your first package....you can select SSIS....Configurations (from the main menu), and define a package configuration. The package configuration allows you to point to about 99% of all the task component and connection manager properties, and define their usage through a configuration.

And then you can create subsequent packages and use the same configuration. Now, there's one main rule....for all 100+ packages, the name of the connection manager in the package must be the same. (When you think about it...if you have 1 config file that configures 2 properties for a connection manager, and that config file is to be shared by 100 packages, the connection manager needs to use the same base name across all packages, or else the config mapping wouldn't work).

That's a short answer, but that's generally what you have to do. Give that a try, and let me know if that helps or not.