February 16, 2017 at 2:29 pm
Hi,
I have many SSIS packages (stored in File System) which are configured to use ENV variable for DB connection. I had to recently change the Env value to use SQL listener. When I open the packages in BIDS, all of them are picking up the new value.
However, when I open the SQL agent job through which I am scheduling these SSIS packages, data sources connection is pointing to old server name instead of the new one from Env variable.
Also, on node A, if I open the package, open the DB connection and click ok and save the package and I am not seeing this value getting changed in the SQL job data sources tab. Same is working fine in node B.
So Is there a way to refresh these data sources without having to open all the packages and opening connection managers and saving them in BIDS and an ideas on what could be the reason on Node A not taking in these changes even after saving them in BIDS?
Any help is appreciated.
Thanks,
-Mohsin
February 16, 2017 at 2:35 pm
I'm wondering if there might be a difference in the value of the environment variable somewhere. The other possibility is that someone logged on as the user that runs these jobs and created a USER environment variable of the same name without realizing the impact, or someone tried to solve the problem that way and forgot to undo it ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 2:58 pm
The Env variable I have created is system variable. Also, this is a new server so someone else creating one is very remote. But I will verify just to make sure. Thanks for your suggestion.
February 16, 2017 at 3:59 pm
Not the expected solution but restarting the server did the trick in my case.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply