Griffster (7/20/2012)
Oh lordee! That's a nightmare! Why would they do that?!So, the best thing to do is, in BIDS, create an environment variable package configuration to point to an xml configuration file outside of the package. Is that right? I've never used an environment variable configuration before really. Once you've done that, how do you set up your SQL Agent job to look at different xml configuration files?
The reason they changed that is so that you can change the connectionstring to the config table/file and that new package configurations are applied. It allows you to change configurations at runtime. But it is pretty confusing 🙂
If you use an environment variable, you don't do anything in SQL Server Agent. Everytime the package changes from environment, it will read the new value from the environment variable and change configurations accordingly.
If you want to change configurations ad hoc in SQL Agent, I wouldn't use an environment variable or package configurations at all. I would simply rely on the configuration file you specify in the job.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP