Handling errors in Data Flow

Data Flow is the most important and useful part of SSIS packages. Data will transfer there, and if errors occur, there should be an error catching method. The importance of such error catching feature is mostly due to the fact that data flows will transfer millions of data rows in nightly schedule, and maybe re-producing an error which occurred at night can't be possible for a second time.

Fortunately SSIS Data Flow uses a powerful error catching mechanism which will act on every data row on most of the transformations, sources, and destinations. When a transformation hits an error on one or more specific data rows, those rows will be caught and we can develop scenarios on how to deal with those rows.

Most of the sources, transformations, and destinations in Data Flow have an Error Output. Data rows which cause error can be redirected in the error output.

In this recipe, we will use a simple ETL scenario. A flat file data row is inserted into a database table, the...