February 11, 2008 at 4:39 pm
I have a scheduled dts job which fails from time to time. When I go to the job properties and then change the process exit code from 0 to 1, the job completes as successful. Again after a few weeks the job failed and looking at the info from the job history, I again changed the process exit code from 1 to 0 and the job completed successfully on its next run.
The same thing happened again, and I changed the process exit code from 0 to 1 today and the job ran successfully.
Can someone tell me what exactly is happening. (Also give me a bit of idea about process exit codes)
Here is the failure message from the job:
"Executed as user: Domain\xxxx. ...Start: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [HPOpenView].[dbo].[NOTES_EXTRACT] Step DTSRun OnFinish: Create Table [HPOpenView].[dbo].[NOTES_EXTRACT] Step DTSRun OnStart: Copy Data from Export_Project to [HPOpenView].[dbo].[NOTES_EXTRACT] Step DTSRun OnProgress: Copy Data from Export_Project to [HPOpenView].[dbo].[NOTES_EXTRACT] Step; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnError: Copy Data from Export_Project to [HPOpenView].[dbo].[NOTES_EXTRACT] Step, Error = 0 (0) Error string: Error during Transformation 'DTSTransformation__162' for Row number 1194. Errors encountered so far in this task: 1. Error source: DTS Data Pump Help file: Help context: 0 Error Detail Records: Error: -2147213269 (8004202B); Provider Error: 0 (0) Error string: TransformCopy... Process Exit Code 1. The step failed."
Heres is the message from the job history when I changed the process exit code from 0 to 1 and the job ran successfully:
"Executed as user: Domain\xxxx. ...rt: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [HPOpenView].[dbo].[NOTES_EXTRACT] Step DTSRun OnFinish: Create Table [HPOpenView].[dbo].[NOTES_EXTRACT] Step DTSRun OnStart: Copy Data from Export_Project to [HPOpenView].[dbo].[NOTES_EXTRACT] Step DTSRun OnProgress: Copy Data from Export_Project to [HPOpenView].[dbo].[NOTES_EXTRACT] Step; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnError: Copy Data from Export_Project to [HPOpenView].[dbo].[NOTES_EXTRACT] Step, Error = 0 (0) Error string: Error during Transformation 'DTSTransformation__162' for Row number 1194. Errors encountered so far in this task: 1. Error source: DTS Data Pump Help file: Help context: 0 Error Detail Records: Error: -2147213269 (8004202B); Provider Error: 0 (0) Error string: TransformCopy... Process Exit Code 1. The step succeeded."
Thanking you in advance.
Murtaza Millwala
February 11, 2008 at 9:29 pm
Here are my input.
1. How often does your job run?
2. You provided the message for status of each step. But it is hard to analyze your question without any information on the job in detail.
3. No mather whether or not your job failed or succeeded, there are errors in steps. You need to fix these errors first.
February 12, 2008 at 4:07 pm
HI,
Thanks for your reply. The job runs daily every morning around 5:00am. However you are right about the errors which show in the job completion message.
The funny thing is that the job completes successfully. The data is transferred to the tables correctly.
The dts was basically transferred from the UAT server to the prod. I saved it to Prod and then made the server and table name changes in the execute task and the data transform tasks.
Should I have created the dts as new? rather than modifying it for the prod server? The job hasnt ever failed on the test server.
February 12, 2008 at 4:52 pm
If you think all the necessary tasks have been executed properly for both 0 and 1 process exit codes, then consider them both successful.
You may want to script it out, accepting the return code to a variable, and if the value isn't 0 or 1, then you raise an error.
_____________
Donn Policarpio
February 12, 2008 at 8:30 pm
You may add a log file in your DTS package to find detail information.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply