SSIS has several places where it controls error handling. It depends on where you look within an ETL process. It can handle error during task execution or Data Flow. Error handling related to tasks is usually handled in the user interface.
SSIS uses OnError error handler for any error occurring within a scope of the SSIS Package execution. This scope however can be reduced to an individual container. Error Handling is treated as any SSIS workflow.
Examples of Error that needs to be handled are:
- Notification that a component failed within SSIS Package
- Task fail to complete
- Forcible changes in the workflow
Handling errors can be done in two different ways:
- Fail entire package is error occur
- Redirect rows with errors to a failed Data Flow section within SSIS workflow
Pic 1: SSIS Error Handling Configuration
Errors are indicated by red error lines within BIDS Interface while you rung your SSIS Package.
Logging Error is very important activity that helps us recover and troubleshoot error effectively. SSIS introduced very fine level of logging. For example, partial logging is now part of SSIS as before it was not an option in DTS. Error logs can be committed to any data container such as SQL Server database, SQL profiler, Text File, Event Log and XML.