DTS fails once a while,works most of time. Last JOB fail was 1 and month ago, any ideas?

Question

Post reply

DTS fails once a while,works most of time. Last JOB fail was 1 and month ago, any ideas?

Yvette Hsu

Newbie

Points: 2
More actions
October 9, 2005 at 10:13 am

#69888

My JOB has 3 steps. 1st-write a batch file, 2nd-excute the batch file, 3rd-run DTS which calls STORE PROCEDURE to import data from text file(from 2nd step) to db. JOB failed last time was 1 and half month ago. And I don't know when the JOB will fail next time.
3rd step fails if the job fails. According to the JOB error log from server(see fingure below, same error log for twice). I suspect the source file is not unziped completely, so it makes the JOB fails. However, I use the identical text file from server to test on my local machine, my JOB success.
I don't think there's problem with my DTS package, because if it has problem, it would not work at all.
Besides, I thought there are too many text files import at the same time(in the morning), the confliction makes the DTS fails(I started the DTS manually without calling from a JOB to avoid factors). So I eliminate all the import link except the smallest importing-text file in the DTS and started DTS manually again. The job failed in the MORNING. Why do I mention MORNING, because it works in the AFTERNOON without any changes(since I could not find out what was going wrong). I am really lost!!!
I suspect some other JOB was running at the same time as my JOB ran at the same server. But I see no other jobs schedule on the server.
Could any one rescue me?
Figure1:

... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_5 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;... Process Exit Code 1. The step failed.