How does error-handling work in SSIS?
The error-handling work in SSIS are done by:
• We transfer millions of records from source to destination during ETL process. There might be one or two multiple data row errors during this transferring.
• These errors can cut off transferring of records and it is important to catch that records that can cause an error, if we want to load proper data to destination without any error records.
• When working with SSIS data flow, SSIS components are divided into three sectors called sources, transformation and destination,
• These data flow components used during ETL process supports error outputs to handle errors.
• We can specify how we want component to behave, in an SSIS component that is if we want an error output during error occured or during truncation occured.
• SSIS allows a developer to create an error output through
which error rows are directed from secondary pipe.
• To another transformation component or destination component, this secondary pipe can be connected as an input.
• We can connect error output from derived column to ADO.NET destination or from derived column to Flat file destination we can choose any combination that suits our requirement.
• ErrorColumns, ErrorCode and ErrorDescription are included in error outputs along with bad rows.
During ETL process we transfer millions of records from source to destination during this transfering there might be one or two multiple data row errors. These errors can cut off tranfering of records and if we want to load proper data to destination without any error records then it is important to catch that records that can cause an error.
Friends, if you have read our previous articles or watched our videos there we have explained you only about output. We have not shown anything related to error which occurs during runtime. If any error occurs in the data flow how to handle that or how to log an error we have not explained you. So in this article we thought you to explain to log an error if it occurs during runtime.
This log error will help us to fix an issue as ASAP or it will help us to know what problem exactly occured.
When working with SSIS data flow, you will get many SSIS component divided in three sectors i.e. sources, transformation and destination. These many data flow components used during ETL process supports error outputs to handle errors. In the SSIS component we can specify how we want component to behave for example if we want an error output during error occured or during truncation occured. SSIS allows a developer to create an error output through which error rows are directed from secondary pipe.
In SSIS error may occur because of the following reasons.
1) Data Conversion errors
2) Look match errors
3) Expression evaluation errors
Dataflow components support row-level error details in both the input as well in the output data. One error output can be connected as the input for the other data component.
Types of errors:
There are two types of errors in SSIS.
A truncation generates results that might be usable. For example, if you trying to insert 10 character values into 5 charter length column.
This is failure of the SSIS component and it generates the NULL result. For example: If you trying to convert alpha-numeric string to Integer. This will result data conversion error.
We can configure the SSIS component how to handle the error and truncation in SSIS transformations.
Fail Component: The Data Flow task fails when an error or a truncation occurs. Failure is the default option for an error and a truncation.
Ignore Failure: The error or the truncation is ignored and the data row is directed to the output of the transformation or source.
Redirect Row: The error or the truncation data row is directed to the error output of the source, transformation, or destination.