DTS Package failing to start when scheduled via a job

Question

DTS Package failing to start when scheduled via a job

nman47

Old Hand

Points: 334
More actions
August 27, 2007 at 9:56 am

#91228

I am a rookie here so please bear with me on this...
I have a DTS package that I created which includes some basic steps (FTP file task...and load the table with its contents). The package works fine when executed on its own but when I excute it with a scheduled job, it fails and fails to give any detailed reason why. I have recreated the jobs and redid the DTS package from scratch but it still doesn't work. Any help is appreciated.

Thanks,
Nimesh

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Nimesh,

This is usually due to permissions. When you execute the package manually, it will run under the context of the user account that you are logged in as. When SQL Agent executes the package, it will execute under the context of the job owner. Open up the job properties and check out who owns the job. It could also be due to the account running SQLAgent on your server, but this problem is usually due to the job owner.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

nman47 Old Hand Points: 334 More actions · Answer 2

I have checked the permission as well and it is setup with the SQL service account as being the owner of the job. The following is the error message I get in the log file.

DTSRun: Loading...

Error: -2147467259 (80004005); Provider Error: 0 (0)

Error string: Unspecified error

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts80.hlp

Help context: 713

John Rowan SSC Guru Points: 56440 More actions · Answer 3

What happens if you set the job owner to 'sa'?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

You also want to make sure that the paths to external files/databases are set up to be visible *as you've defined* from the perspective of the user running SQLAgent. Meaning - if you need to access a file on a local drive on the server - the SQLAgent service account a. needs permissions to that file/folder, and b. the data/file connection needs to be set up to get to that file in a way that the local service account can use (e.g. if the agent service account is not a local admin, using a d$ share wouldn't work, for example, even if the account has access to the d: drive through direct file permissions.)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?