Informatica PowerCenter is given with a set of options to take care of the error handling in your ETL Jobs.
Error Handling Functions
We can use two functions provided by Informatica PowerCenter to define our user defined error capture logic.
ERROR (): This function causes the PowerCenter Integration Service to skip a row and issue an error message, which you define. The error message displays in the session log or written to the error log tables based on the error logging type configuration in the session. You can use ERROR in Expression transformations to validate data. Generally, you use ERROR within an IIF or DECODE function to set rules for skipping rows.
Ex: IIF (TRANS_DATA > SYSDATE, ERROR ('Invalid Transaction Date'))
Above expression raises an error and drops any record whose transaction data is greater than the current date from the ETL process and the target table.
ABORT (): Stops the session, and issues a specified error message to the session log file or written to the error log tables based on the error logging type configuration in the session. When the PowerCenter Integration Service encounters an ABORT function, it stops transforming data at that row. It processes any rows read before the session aborts. You can use ABORT in Expression transformations to validate data.
Ex: IIF (ISNULL (LTRIM (RTRIM (CREDIT_CARD_NB))), ABORT ('Empty Credit Card Number'))
Above expression aborts the session if any one of the transaction records are coming without a credit card number.
Default Port Value Setting
Using default value property is a good way to handle exceptions due to NULL values and unexpected transformation errors. The Designer assigns default values to handle null values and output transformation errors. Power Center Designer let you override the default value in input, output and input/output ports.
Default value property behaves differently for different port types;
1. Input ports: Use default values if you do not want the Integration Service to treat null values as NULL.
2. Output ports: Use default values if you do not want to skip the row due to transformation error or if you want to write a specific message with the skipped row to the session log.
3. Input/output ports: Use default values if you do not want the Integration Service to treat null values as NULL. But no user-defined default values for output transformation errors in an input/output port.
Default Value Use Case
Use Case 1
Below shown is the setting required to handle NULL values. This setting converts any NULL value returned by the dimension lookup to the default value -1. This technique can be used to handle late arriving dimensions.
Use Case 2
Below setting uses the default expression to convert the date if the incoming value is not in a valid date format.
Error Handling Settings
Error handling properties at the session level is given with options such as Stop on Errors, Stored Procedure Error, Pre-Session Command Task Error and Pre-Post SQL Error. You can use these properties to ignore or set the session to fail if any such error occurs.
Stop on Errors: Indicates how many non-fatal errors the Integration Service can encounter before it stops the session.
On Stored Procedure Error: If you select Stop Session, the Integration Service stops the session on errors executing a pre-session or post-session stored procedure.
On Pre-Session Command Task Error: If you select Stop Session, the Integration Service stops the session on errors executing pre-session shell commands.
Pre-Post SQL Error: If you select Stop Session, the Integration Service stops the session errors executing pre-session or post-session SQL.
