Unanswered: DTS Package Error Executing in SQL Agent
I just got assigned to help out with a couple of problems with the running of DTS packages using the SQL Server Agent, this is the problem and a list of what I've done so far:
There are 4 packages that:
a) Deletes all records from a table and then populates it using a flat file that contains that day's transactions.
b) Creates a report in Excel
c) Creates a summary report in Excel
d) Does a), b) and c) for another table.
All these packages are scheduled to run weekdays starting at 8:00 am, and they were owned by sa. Last month we had a massive change of passwords thanks to an audit and unfortunately the dba left without letting us know that password. After the massive change, these packages started failing with errors like:
The job failed. The Job was invoked by Schedule 18 (DLYINVDT_XLS). The last step to run was step 1 (DLYINVDT_XLS).
The job failed. The Job was invoked by Schedule 22 (AGAR830_dts). The last step to run was step 1 (AGAR830_dts)
Yesterday I went into all the packages and changed the connection properties from user sa to another user that has permission over the tables and ran the 4 of them at least 10 times each, both using my personal laptop and directly in the server (just wanted to check remote and local, even though I know it really has nothing to do with the execution that its always local to the server).
Now, the funny thing is that I came in this morning and guess what..... yeap, the packages failed, I can run them manually with NO problem at all, but if I run them using the SQL Server Agent then they just will not run.
At the time I was writing this I found out the password for sa, so now I'm using it but unfortunately no change, the errors now are :
The job failed. The Job was invoked by User sa. The last step to run was step 1 (AGAR830_dts)
I'm an informix dba and I started training myself in sql a couple of months ago so I'm confortable with what I'm doing, but at this point in time I'm about to throw something to the screen Since I can execute the packages manually then the users are happy, but I would really like to find out why the Agent is not working right.
To anyone interested.... I solved the problem by recreating the DTS packages and rescheduling them, they work perfect now, would still like to know why the previous problem happened, but oh well, as long as its working now
Generally, when Enterprise Manager creates a dtsrun job, the command is created with the DTS package ID. The ID goes with the previous version of the package. I usually change my jobs to run the package by name, so it always picks up the latest version.