DTS Package fails when scheduled as a job

Question

Post reply

DTS Package fails when scheduled as a job

Ms CRB

Mr or Mrs. 500

Points: 528
More actions
January 24, 2006 at 10:27 am

#167267

I have a simple dts package set up that imports a text file into a table. The package has been working for months and now all of a sudden it stop working and is giving the following error. The system cannot find the file specified the file is located on the e: drive of the same server. It executes fine if I execute the package directly in dts but it's failing running the scheduled job in sql agent. All of the dts jobs are failing with the same error. I set up a new dts job to test, I created a text file with three rolls and imported it into the northwind database same thing happened. It ran fine in the dts but failed as a job. I've tried running this as sa with the password and as an administrator both resulted in the same thing. The system cannot find the file specified. Anyone have and idea on how I can resolve this or why this may be happening.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chris Mackin-166947 SSChasing Mays Points: 653 More actions · Answer 1

See this article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;269074

I had this problem a little while ago, bottom line is that if the Job is running under the permissions of someone with Admin permissions then the job is actually not going to run as an admin, rather it will run under the permissions of the account that's running SQLAgent. So you need to make sure that user has the proper permissions. I would suspect that security settings, either for the user running the Job or the account that SQL Agent runs under were changed causing it to stop working.

Good Luck,

Chris Mackin

Fra4nklin German Valued Member Points: 59 More actions · Answer 2

Also, make sure that you use UNC for your network drives. I have experienced a lot of problems when using mapped drives.

Ms CRB Mr or Mrs. 500 Points: 528 More actions · Answer 3

Thanks everyone I resolved the issue of the dts not working when scheduled as a job. It was discovered that the system path on the server to the dts executable was missing. I'm not sure exactly what happened but something went on with the server that resulted in the removal of that path. To test this I edited the step in the dts and fully qualified the path to the dts executable, after adding that the job ran successfully. The solution was found in the Knowledge Base

http://support.microsoft.com/?kbid=322746

For example:

"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /~E080989ED67CC54E9931C07DC3282FDA

To keep from having to do this for every job the path was added back to the system path. How this path got removed in the first place is a mystery that no one is claiming ownership for.