Error Handling and Logging in SSIS

 

1. Error Handling in SSIS

A. Precedence Constraints (Success, Failure, Completion)

• Control the flow of tasks based on execution status:

  • Green (Success) – Proceed if the previous task succeeds.

  • Red (Failure) – Execute if the previous task fails.

  • Blue (Completion) – Execute regardless of success/failure.

B. Event Handlers

• Execute custom logic when specific events occur:

  • OnError – Runs when a task fails.

  • OnWarning – Runs when a warning occurs.

  • OnTaskFailed – Runs when a task fails.

  • OnPostExecute – Runs after a task completes successfully.

Example:

• Log errors to a database or file when OnError is triggered.

C. Error Output in Data Flow

• Configure error outputs for transformations and destinations:

  • Ignore Failure – Skip the error and continue.

  • Redirect Row – Send failed rows to an error output.

  • Fail Component – Stop execution on error.

Example:

• Redirect bad rows to an error table for later analysis.

D. Transactions & Checkpoints

• Transactions: Use TransactionOption to roll back on failure.

• Checkpoints: Restart packages from the point of failure.