Error Handling and Recovery
Sessions fail for predictable reasons - bad data, constraint violations, deadlocks, connectivity - and this domain tests how you detect, contain and restart them.
Row-level handling
The Expression transformation provides ERROR('message'), which raises a row error and continues, and ABORT('message'), which stops the session. Rejected rows are written to the session's reject file with a reject code, so always set the reject file directory explicitly.
Session-level handling
The session's error handling properties decide tolerance: Stop on errors sets how many non-fatal errors are allowed before the session stops, and zero means never stop. Tracing level (Normal, Terse, Verbose data) controls log detail, and the session log is the first place to look.
Load behaviour
Treat Source Rows As controls overall behaviour: Data Driven respects the Update Strategy (DD_INSERT, DD_UPDATE, DD_DELETE, DD_REJECT), while Insert, Update, Delete and Update as Update override it at session level. Update overrides on the target handle special cases.
Recovery
Enable recovery on the session so it can be recovered after failure. The recovery strategy is either Resume from last checkpoint (restart from the last commit point, re-reading relational sources from the checkpoint) or Restart task (start again). Workflows can be recovered, suspended and resumed, and failover can move a run to another Integration Service node.
Worked example
A 10-million-row load with a commit interval of 10,000 fails at row 7.4 million. With recovery enabled and Resume from last checkpoint, the recovered run starts from the last commit rather than row 1 - but only if the source can be re-read from that point and no bulk load left the target half-updated.
Traps
- Using ERROR() or ABORT() outside an Expression transformation.
- Setting Stop on errors high and assuming failures are harmless.
- Recovering a session whose target was bulk loaded: checkpoints and bulk loads do not mix well.
- Expecting a suspended workflow to finish on its own; it waits for a resume.