Here is the deal. I created a DTS package, and schedule it. A job is created. However when the job runs at it regularly scheduled time, it fails. Yet I can go directly to the package and execute it, and it works flawlessly. What is up with that? What am I doing wrong? How can I fix this? Any Ideas would be greatly appreciated.
It doesn't really matter what I am doing, because I can't get any of them to work, but here goes.
In one of the packages, I am simply bringing data from a text file into an Sql table. Nothing fancy.
In another package I am getting data from a Sql Server Offsite, and bringing the data to my location, and inserting it into a Oracle table. Before I do that, I am verifying that I can connect to the Sql Database, If that is succesfull, I am truncating the Oracle table, and then importing the data. Like I said, if I execute the packages themselves, they work great. If I schedule them they fail.
Here is the Job History for one of them. They both say the same thing
The job failed. The Job was invoked by User Domain\Username. The last step to run was step 1 (TableName).
See Attached file from Sql Server Event log. Thanks for all your help.
Couple of things to check:
1. Does sql agent service account have permission on text file? Deos it have pemission to access remote sql server?
2. When you execute package directly, do you execute it on the server or on your desk via sql client? If from your desk, does sql server have same version of mdac as on your desk?
The account that I am using is a Domain admin account on my domain, so it has permission to the (Local)text file. I don't think that the account that I am using has permisson on the remote Sql server or Domain. Should that matter though? In the package sonnections I am giving it the username and password that have the permissions needed.
I have tried to run these packages both from my computer, and from the server. I believe that both computers have MDAC 2.6. One thing that I have neglected to mention is that the Sql server that I am getting the dat a from is 6.5, The server that I am running the package on is 7. Would it be easier for either you to call me, or me call you? Let me know. my e-mail address it Troy.Rozeske@fhr.com. Thanks again for all of your help.
DTS Scheduling Problem
Reply With Quote
