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.

• 
  

2. Logging in SSIS

A. Built-in SSIS Logging

• Log events to:

  • SQL Server (msdb.dbo.sysssislog)

  • Text/CSV files

  • Windows Event Log

  • XML files

Steps to Enable Logging:

1. Right-click the package → Logging.

2. Select a log provider (e.g., "SSIS Log Provider for SQL Server").

3. Choose events to log (e.g., OnError, OnWarning).

B. Custom Logging (Using Script Task or SQL)

• Script Task: Write logs to a custom table.

• Execute SQL Task: Insert logs into a logging table.

Example (SQL Logging Table):

sql

Copy

Download

CREATE TABLE SSIS_Logs (
    LogID INT IDENTITY(1,1),
    PackageName NVARCHAR(100),
    TaskName NVARCHAR(100),
    ErrorCode INT,
    ErrorDescription NVARCHAR(MAX),
    LogDateTime DATETIME DEFAULT GETDATE()
);

C. Logging with SSIS Catalog (SSISDB)

• If using Project Deployment Model, logs are stored in:

  • [SSISDB].[catalog].[executions]

  • [SSISDB].[catalog].[event_messages]

  • [SSISDB].[catalog].[operation_messages]

Query logs:

sql

Copy

Download

SELECT * FROM [SSISDB].[catalog].[executions] 
WHERE status = 4; -- Failed executions

D. Logging Best Practices

1. Log critical events (errors, warnings, task completions).

2. Use variables to capture dynamic values (e.g., row counts, file names).

3. Archive logs periodically to avoid bloating.

4. Use SSIS Catalog for centralized logging in enterprise environments.

---

3. Common Error Handling Patterns

A. Redirecting Bad Rows

• Use Error Output in Data Flow to capture bad records.

• Store them in an error table for analysis.

B. Retry Logic

• Use a For Loop Container to retry failed tasks.

C. Email Notifications

• Use Send Mail Task in OnError event to alert admins.

---

Conclusion

• Error Handling: Precedence constraints, event handlers, and error outputs help manage failures.

• Logging: Built-in logging, custom tables, and SSISDB provide audit trails.

• Best Practices: Redirect bad rows, use transactions, and implement notifications