DTS Package Scheduling error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Data Transformation Services (DTS) » DTS Package Scheduling error

DTS Package Scheduling error

Rate Topic

Display Mode

Topic Options

Author

Message

sheteamit

Posted Monday, January 21, 2008 6:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 9:37 AM
Points: 4, Visits: 13

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 :)

Post #445444

schumi-440251

Posted Monday, January 21, 2008 8:57 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 11:56 AM
Points: 33, Visits: 178

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

Post #445704

sheteamit

Posted Tuesday, January 22, 2008 2:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 9:37 AM
Points: 4, Visits: 13

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.
**************************************

Post #445781

sheteamit

Posted Tuesday, January 22, 2008 3:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 9:37 AM
Points: 4, Visits: 13

Hi John

Yeah you are right, If I I run the SQLSERVERAGENT using 'Administrator' account then the DTS job runs fine.

Thanks a ton for your feedback. :)

Amit

Post #445801

schumi-440251

Posted Tuesday, January 22, 2008 9:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 11:56 AM
Points: 33, Visits: 178

So did it work out for you?

John

Post #445955

sheteamit

Posted Tuesday, January 22, 2008 9:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 9:37 AM
Points: 4, Visits: 13

Ohh yeah...it works without any problems !

Post #445957

« Prev Topic | Next Topic »

Permissions