Analytics Software

Hi ODI Experts and Guru's,

I am fairly new to ODI and I would like to request your assistance and your expertise regarding ODI.

Scenario: I have a flat files with multiple fields and one of the column is a date. In the example below, the date column is field3.

Flat file delimited by semicolon:
field1(string) ; field2(string) ; field3 (string)
1111 ; 111134 ; 2076-05-11 16:00:00
1112 ; 234134 ; 2010-06-21 12:14:37
1113 ; 342221 ; ###

Oracle Target Table:
field1(numeric) field2(numeric) field3(date)

For some reason, our source flat file with the column name field3 which has a date datatype has a value of "###". So during execution of the interface that will populate the Oracle Target Table, I always encounter an error in the steps "Insert flow into I$ table" with oracle error "Caused By: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0". I understand that the value "###" is an invalid date value and I would like some help on how to capture the error and put the record that has a problem in the error table instead of stopping the whole integration process. Since I will be transferring 1terabyte of data everyday, I don't want the risk to stop the whole integration because of the single problem in the row. I have tried adding a condition in the source file datastore but with no effect. Is there a way to capture the row with a problem and put in it in the error table and let the integration process continue?

Your help and guidance will be highly appreciated.

Regards,

Russel