Unanswered: Why are my scheduled DTS packages failing???
I have a section of about 6 DTS packages that started failing recently. They all write to a network share using a UNC path. The failure coincides with a change to the account SQL Server and SQL Agent run under. I know security context is very important and I have verified the new service account they run under is a Domain account that can execute the DTS packages and has full permissions to the network share.
But when the Agent Job tries to run it fails everytime. Two of us have been looking at this and can't find the issue.
NOTE: I also made the service accout a Server Administrator (fixed server role) to make sure it wasn't a SQL permissions issue.
What color is the smoke? You tell us that it won't run, but we don't have a clue what the problem is from your description...
When you look at the SQL Agent History for the job, what diagnostic messages appear? Does the job start? Does the DTS package start? Does the machine belch blue smoke, emit shrill sounds and foul odors?
I understand that you are frustrated. This kind of problem can be really vexing. I can't help you without at least a few more clues though.
Here's the basic failure information...rinse, rather, repeat for each step of the package:
DTSRun OnError: Drop table Results Step, Error = -2147467259 (80004005)
Error string: The Microsoft Jet database engine cannot open the file '\\sharedFolder\public\Finance - Daily Reports\CampusVue Reports\EnrollMastExpStartDate.xls'. It is already opened exclusively by another user, or you need permission to view its dat
Error source: Microsoft JET Database Engine
Help context: 5003051
The problem is that another user/process (that is probably having nothing to do with your DTS package at all) has opened the Excel file when the DTS package tries to run. Odds are that the DTS package is not the problem but that some bozo that opened the file then wandered away from their computer probably is your problem!
Depending on the OS that supports the drive where your Excel file is stored (apparently a version of MS-Windows) and the version of the Excel executable, this could be easy or hard to diagnose for you as a user... I would try to open the file with another copy of Excel to see if you get any meaningful diagnostic message from it (telling you who or what already has that file open). If that doesn't help you, you'll need to get a Windows Administrator (probably your "geek in residence") to help you track down and kill the offender!