DTS fails with Unspecified error when scheduled and ran through a job

Question

DTS fails with Unspecified error when scheduled and ran through a job

Chris Patton

Valued Member

Points: 63
More actions
November 8, 2005 at 8:16 am

#169729

I have a DTS that fails with the following error. It is scheduled to run in a job with the SQLService account as the job owner. The SQLServerAgent service also runs under the same domain SQLService account. When I run the DTS manually I normally do not have an issue, although occassionally when I attempt to run it manually I do get an error message that says 'Unspecified error' and I can't even open it up in design mode.
The other thing I noticed is that I do have an error log setup on the DTS and when it fails with this error it does not even write to the log file. One more detail is that in the DTS there is a Fire Transfer Protocol Task that transfers an Access mdb file from a virtual server that runs on VM ware (I am trying to verify the details of this with my network group). I am thinking this task in the DTS might have something to do with the error. Any help will be greatly appreciated:

Executed as user: NEWCENTURY\SQLService. 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. Process Exit Code 1. The step failed.

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I would separate internal and external processes.

FTP is dependent on another server being running and accessible, network configuration the same etc. I would ( and I do) run FTP download using Windows scheduler and then as an independent job I do data import.

Yelena

Regards,Yelena Varsha

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

80004005 usually indicates a problem with OleDB/MDAC.

When you are running manually, are you logged on to the server (i.e. the machine where Sql Server is running) or another computer, such as a workstation.

Are you logged on with the same account as being used by the SQL Agent ?

What is the Windows edition and version of the server ? As some Windows 2003 service packs do patch OleDb/MDAC, a recent sp without the patch may be the cause of the error.

SQL = Scarcely Qualifies as a Language

Chris Patton Valued Member Points: 63 More actions · Answer 3

Thanks for the suggestions. I still never got any word back from our network group. A temp fix we put in place within the DTS was to take out the File Transfer Protocol task and replace it with a Execute Process Task that calls a batch file to do the file copy process. Weird that the File Transfer Protocol task was causing issues.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

I had similar experience with jobs, when ActiveX script job would not run with a simple VBscript, but when the same code is put in vbs file and Operating System Command type is selected (which is similar to Execute Process task) with a command text "cscript mycode.vbs" it works fine.

Also, did you check the identity on every step for both types of tasks? Basically, what account is running the show on every step?

Regards,Yelena Varsha