DTS Package Scheduling error

Question

DTS Package Scheduling error

sheteamit

SSC Enthusiast

Points: 106
More actions
January 21, 2008 at 6:52 am

#182210

I have a DTS package which runs fine when I run it manually from the Enterprise Manager. When it is scheduled as a job on the server at specific times it fails with the following error. :w00t: The packge logs reads as below.
****
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Invalid Task Result value.
Step Error code: 80040401
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
****
The owner of the job is 'sa'. I tried changing the owner to Administrator still the problem persists.
The first step in the package is trying to copy one file from a network srive to another folder using a Vb Script with the fileSystemObject's Copy method.
Also another problem is as follows
It the script is written with the source file path as (Q:\..\test.txt) then it works as the Q:\ is mapped on my workstation. If i change it to the full path i.e. (\\ken-aad-004\testfolder\..\test.txt) then the script fails it says invalid file path.
Can somebody provide some views on this please ?
Thanks
Amit 🙂

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

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

schumi-440251 Mr or Mrs. 500 Points: 515 More actions · Answer 1

I had many problems like this, DTS not running as a job. First thing comes to my mind is access rights. Break down the DTS and try each task alone and schedule it as a job to find which one fails. When the DTS runs as a job it uses the SQL_service (the account of the SQL agent, could be different user on ur machine) to get access rights to folders/files/executables. Add this user (SQL_service)to have read/write rights on that folder/file. also, r u using relative path? avoid map drives and use physical paths as much as you can, but keep in mind that when the DTS is a job it runs on the server, so when u say D:\ , the server should have the D:\, while if u run it from enterprise it is the D:\ of the pc you have the enterprise running on.

hope this helps

john

sheteamit SSC Enthusiast Points: 106 More actions · Answer 2

Hey John

Thnks for the reply. Yes, I have tried the SQL service agent properties to look for the access rights. THe Job is scheduled by user 'sa'. Also the SQL service is run by 'SYSTEM'. SYSTEM user has Full rights on the folder in question.

I am also giving the complete network path and not the mapped drives. Below is the Error Log.

Can you figure out something from this ?

**********************************

Executed as user: KEN-SQL-002\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

**************************************