Scheduled DTS pkgs fail to execute

Question

Scheduled DTS pkgs fail to execute

janevohden

SSC Rookie

Points: 33
More actions
September 2, 2004 at 2:41 pm

#162150

I have generated a DTS pkg that executes successfully when I run it manually but fails when I schedule it. Why is this? Where can I go on the system to find a meaningful message as to what is happening?
Thanks for any help you can give me. -Jane

Viewing 8 posts - 1 through 7 (of 7 total)

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

Glen G SSC Rookie Points: 36 More actions · Answer 1

Hi,

I dont know if this will answer your question? But I assume you have scheduled the DTS package, and that this is showing in Enterprise Manager as a job (ie under Management, SQLserver Agent, Jobs).

Select the Job in question, right mouse click and select "View job history.." then in the history window, select the "Show step details" at the top RH corner. This will display the DTS steps, and the step that failed. Select the step that failed, and the error details display at the bottom.

Good luck!

Intranet Grasshopper Points: 18 More actions · Answer 2

Maybe the machine from wich you run the DTS is different than the SQL server one, and the DTS is looking for some resources located on your local machine (file, environment variable, net use...).

Bye.

MurphRally SSC Journeyman Points: 89 More actions · Answer 3

I have encountered a similar issue with trying to access files or data on a different machine from the main SQL Server Box. Reason for this is the difference in security and/or access rights from your local machine and login and what the Login the job is running from the SQL Server.

Was able to solve one of the issues by adding an additional step to the frontend of the job calling "NET USE [ServerPath]" of the machine I was trying to access. This solved the problem on one server (non-clustered) but the attempt of the same job on a clustered server failed.

Interested in hearing if you have any additional luck.

amackay_1 Ten Centuries Points: 1240 More actions · Answer 4

This thread might shed some more light on the issue:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=110&messageid=110996#bm111144

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 5

Depending on the error generated, it is probably an access issue -- the account that the SQL Server agent runs under does not have the proper privileges. What happens is that when a DTS is executed via Enterprise Manager on your local machine, it uses the credentials of your login. When the DTS is executed via a scheduled job, it executes under the credentials of the agent. If this account does not have the proper privileges, the job will fail, whereas when executed locally, it will run fine.

ericp37 SSChasing Mays Points: 631 More actions · Answer 6

Is your SQL Server agent started?

Scheduled tasks wont run without this service started.

Just an idea, let us know the resolution

Eric

janevohden SSC Rookie Points: 33 More actions · Answer 7

Thank you all for your helpful replies! I figured out what was happening by turning on the package logs. The DTS package could not find the file being referenced. I was using a network directory that I had connected as a specific drive letter. The DTS package did not have the same connection relationship defined within SQL server. What I ended up doing was using a fully qualified name (instead of a mapped drive letter) for the server and it worked.