jdurandt (7/28/2009)
Earlier in this comment list it is suggested that the program_name() function be used to separate the different environments. I do not think that this is possible, as the connection used for reading SQL Server configurations is not configurable, i.e. you cannot change the "Application Name" in the scheduled job for that connection.
You can configure the connection used for SQL configuration, but not with a command-line configuration. Command-line configs are applied after the other package configs. The SQL Agent SSIS Task builder is just a fancy interface for creating DTEXEC commands, with any changes you make added as command-line parameters.
You can use indirect configuration to get the SQL Config connection string from an environment variable, for instance, assuming that config item appears earlier in the list than the SQL config items. Then you could take the DTEXEC command from your existing SSIS job step and put it in a bat file after some SET commands for the environment variables.
Not that I'm recommending this approach, I just had to mention it as a possibility. Every additional level of indirection makes it more difficult to manage and debug.