Job Execution sometimes failed

Dear All,
I have a problem with sql server jobs.
The jobs are executing DTS Package in scheculed manner.
The problem is the sometimes the schedule failed, and i don't know what
made the jobs failed.
And I have tried to trace it, but still coundn't find the problem.
Does anyone can help me out?
Thanks
Robert Lie
Try switching on Package logging in your DTS Packages. Right click in the
DTS Package, choose Package Properties and then Logging. When the job fails
again, you can then check the Package log (right-click the DTS Package in
Enterprise Manager, then choose package logs...). Hopefully you will find
the answer in there.
Jacco Schalkwijk
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%2397xWRfuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a problem with sql server jobs.
> The jobs are executing DTS Package in scheculed manner.
> The problem is the sometimes the schedule failed, and i don't know what
> made the jobs failed.
> And I have tried to trace it, but still coundn't find the problem.
> Does anyone can help me out?
> Thanks
> Robert Lie
|||This is the error message when the job failed:
Executed as user: <domain>/<user>. ...un OnStart:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTS... Process
Exit Code -1073741819. The step failed.
Thanks
Robert Lie
Jacco Schalkwijk wrote:
> Try switching on Package logging in your DTS Packages. Right click in the
> DTS Package, choose Package Properties and then Logging. When the job fails
> again, you can then check the Package log (right-click the DTS Package in
> Enterprise Manager, then choose package logs...). Hopefully you will find
> the answer in there.
>
|||For the pump, you can set it to allow a certain number of errors, then log
these errors to a file, this should help you find what the pump is failing
on
If it is a Transform Data Task...Select The Properties..Then the Options
Tab...Enter an Exception File....Set Max Error Count to SOmething Like
9999...Run The Step...Review The Exception Log
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:OrpONQguFHA.1472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> This is the error message when the job failed:
> Executed as user: <domain>/<user>. ...un OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
> DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
> DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
> DTSRun OnStart: DTSStep_DTS... Process Exit Code -1073741819. The step
> failed.
>
> Thanks
> Robert Lie
> Jacco Schalkwijk wrote:
|||Ok. I'll try your idea anyway.
I want to tell you how the DTS Package works:
1. Disable ALL contraint from all tables in a database
2. If success then continue to delete all the tables
3. Fill the deleted tables from another tables from another server
4. Update some values in certain tables
Maybe you see something wrong in that work flow?
Thanks
Robert Lie
David J. Cartwright wrote:
> For the pump, you can set it to allow a certain number of errors, then log
> these errors to a file, this should help you find what the pump is failing
> on
> If it is a Transform Data Task...Select The Properties..Then the Options
> Tab...Enter an Exception File....Set Max Error Count to SOmething Like
> 9999...Run The Step...Review The Exception Log
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:OrpONQguFHA.1472@.TK2MSFTNGP15.phx.gbl...
>
>
|||do you actualy delete/recreate the table, or do you use TRUNCATE, and also,
why dont you do the updates in the DTS Transformation, maybe then you can
keep contrainsts
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ei%23VHiguFHA.2620@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Ok. I'll try your idea anyway.
> I want to tell you how the DTS Package works:
> 1. Disable ALL contraint from all tables in a database
> 2. If success then continue to delete all the tables
> 3. Fill the deleted tables from another tables from another server
> 4. Update some values in certain tables
> Maybe you see something wrong in that work flow?
> Thanks
> Robert Lie
>
> David J. Cartwright wrote: