June 3, 2010 at 10:22 am
I'm having an issue with some SSIS 2008 packages. Since we don't seem to have an SSIS forum for 2008, I'm posting it here. Any guidance would be most appreciated.
What we have is a master package that calls a series of subpackages. The subpackages have various connection settings set via package configurations that point to environment variables on the server where the packages are deployed.
Everything has been working fine, until yesterday when we changed the values of some of these system environment variables that are used by the configurations. Now, when we run the packages via a Windows Scheduler job, the packages fail. The logs indicate that the connections are pointing at the old (now invalid) locations of the source files that are used in the packages. However, when I run the same packages in BIDS, everything works fine.
When running in BIDS, I am remote desktopped onto the application server where the packages are stored. I am using the same user account that is used by the Windows Scheduler job, which is also located on this same server. So I don't think it's an issue with differing user contexts when the scheduled job runs versus when it is run manually in BIDS. Something else seems to be at work here. When I view the packages in BIDS, I cannot find any references to the old fileshare that is showing up in the package logs when run as a scheduled job.
This is very frustrating, and it's only happening in our prod environment, which is making it more difficult to diagnose. Has anyone run into a similar situation? If so, how did you resolve it?
Thanks
June 3, 2010 at 2:56 pm
The first thing that comes to mind is to make sure you updated the configurations in the packages. It sounds like you're saving file paths in the configurations? What type of configuration are you using?
Greg
June 3, 2010 at 3:05 pm
Thanks for responding. The configurations use environment variables on the server. Those variables have been updated correctly, and the packages pick up the updated values when viewed/executed in BIDS. However it seems like the new values are not getting picked up when the package is executed by the Windows Scheduler.
June 3, 2010 at 9:09 pm
More weirdness: If I run the scheduled job manually (right-click on the job in the list of windows scheduled jobs) and run it, it works fine. But if it runs as scheduled, it fails. Thoughts?
June 4, 2010 at 7:19 am
It sounds like the task scheduler is caching your environment variables. I Googled that and came up with this: http://msdn.microsoft.com/en-us/library/aa380715(VS.85).aspx
That's probably whats going on. I would assume that bouncing the Task Schdeuler service would correct this.
June 4, 2010 at 12:23 pm
Caching comes to mind but the other thing that came to mind was did you set the SERVER variables and not the individual versions..
CEWII
June 4, 2010 at 12:37 pm
Thanks for the responses. It definitely seems like it could be caching at work here. Fits all the symptoms we've been seeing. I have restarted the Task Scheduler service, but I won't know if it worked until the overnight jobs run again tonight.
Yes, these are the system environment variables, not individual user environment variables.
I'll post an update when I know if it worked or not...
June 6, 2010 at 11:47 am
Hi
We had a similar issue in our Production environment esp when moving the SSIs packages from QA environment to Production environment. With the limitation of rebooting / restarting the services esp during business hours.
the approach we follow the following steps
1. Take file backup of the current SSIS package in Production
2. Delete the SSIS package from Production
3. Copy the SSIS new package and setup its permissions etc.
If you have any config file then it needs to be setup
I hope this helps.
June 6, 2010 at 1:01 pm
Thanks for the additional ideas. It definitely seems to be a problem with the configuration. I added some code to capture the values assigned to variables in the packages at runtime by the configuration, and they are the old values (even though the relevant environment variables are set to the appropriate new values). So it seems to be some kind of caching problem. I tried restarting the task scheduler service but that didn't fix the problem. I might try SSIS_NewBi's suggestion, though it doesn't seem to be an issue with the packages themselves, since the configurations work if I run the scheduled task job manually. I wonder if a server reboot would help.
For now, I simply disabled the configurations in the packages. The variables in the packages have the correct values in them (added manually by me). The packages now work if the configurations are turned off, even if scheduled. Gives me some time to try some other things at least. (I can't reboot the server without it being a big deal, as this is a production app server.) The mystery continues...
June 6, 2010 at 7:16 pm
It wouldn't be the Task Scheduler service that needs a restart, it woud be SQL Server Agent..
CEWII
June 6, 2010 at 9:20 pm
Why would SQL Server Agent need a restart? Just to clarify, these packages are being executed via the Windows Scheduler, not via SQL Server Agent jobs.
June 7, 2010 at 4:05 am
Did you try deleting the package. the only reason I would recommend is because once you delete it . Internally the package id and all its related instances are killed . This remove anything from cache.
Please let me know. If restarting SQL Server agent works for you then i could also try in upcoming release.
June 7, 2010 at 10:16 am
Hi NewBi
I tried your approach of deleting and copying the packages back over. Did not make any difference. Thanks though.
June 7, 2010 at 10:29 am
Unfortunately it sounds like nothing short of a server restart is going to clear this headache for you. This issue you have discovered is making a good case to me for not using environment variables to store package configs. I wonder if the same behavior occurs when packages are scheduled via the SQL Agent?
We run a few dozen packages daily and change many of the package variables values daily. However, I use the SQL Server [SSIS Configurations] table to store my package configs and run some sql queries to update the values. This has worked well in our situation.
June 7, 2010 at 12:21 pm
Instead of environment variables I like to chose a location on the file system that I use on each server. Then the packages all point to the same place. And then the dtsconfig files point to the right servers for that environment.
CEWII
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply