DTS package via SQL agent trouble...

[song2siren]

Hello

I've got a DTS Package set up to copy some tables from one SQL server to another. However, the job I've set up via the SQL agent seems to run successfully, but doesn't actually perform the required task.

The step has the following message:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1 DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147220413 (80040443) Error string: Error source: Help file: Help context: 0 Error Detail Records: DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1 DTSRun: Package execution complete. Process Exit Code 0. The step succeeded.

I'm not sure if this is a rights issue as the job doesn't fail - it just doesn't run the package at all. Any help/suggestions would be very much appreciated.

Thanks

 

[19511950]

Hello,

I've got the same problem. The DTS successfully execute from 'Data Transformation Services', but when DTS schedule to run in SQL Server Agent it gives the same error as above.

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: The system cannot find the path specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 3 (3); Provider Error: 3 (3) Error string: Error opening datafile: The system cannot find the path specified. 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.

Is anybody can help, please.

thanks.

 

[jhall01]

Check your paths. When executing DTS's through Designer it executes locally, When you setup a Job it executes on the server. If your path in the designer is: C:\documents and setting\19511950\file.txt, which is your local directory it will run fine when executing in the designer, but that exact directory may not exist on the server.

You will need to correct the file location to point to the server location, not the client local location.

 

[19511950]

Thanks jhall01,

The problem still exist.

The DTS package was created locally under my id and I have all rights as administrator.

DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: Access is denied.
Error Detail Records: Error:5 (5);

The package was create locally under my id, but I have all rights as administrator.

thanks again.

 

[jhall01]

check the owner of the job. Try setting the job owner to "sa" and see if that fixes the problem.