Recently DAC task is failing and we saw below error message in the Informatica session logs.
TRANSF_1_1_1> TE_7007 Transformation Evaluation Error [<> [TO_DATE]: invalid string for converting to Date
… t:TO_DATE(s:CONCAT(s:CONCAT(s:SUBSTR(s:TO_CHAR(f:220920220000),i:0,i:8),s:’-‘),s:SUBSTR(s:TO_CHAR(f:220920220000),i:9,i:6)),s:’YYYYMMDD-HH24MISS’)]
TRANSF_1_1_1> CMN_1761 Timestamp Event: [Wed Aug 17 05:28:22 2022]
TRANSF_1_1_1> TT_11132 Transformation [CYCLNS_Trans] had an error evaluating output column [PROMISED_ON_DT]. Error message is [<> [TO_DATE]: invalid string for converting to Date
… t:TO_DATE(s:CONCAT(s:CONCAT(s:SUBSTR(s:TO_CHAR(f:220920220000),i:0,i:8),s:’-‘),s:SUBSTR(s:TO_CHAR(f:220920220000),i:9,i:6)),s:’YYYYMMDD-HH24MISS’)].
After running the sql queries generated by Informatica ETL mapping, it was found that for some values of PROMISE_DATE, the TO_CHAR conversion of the date is adding 00 instead of 20 for the year value so when converted to TO_NUMBER for further processing, it is missing the first two digits of year value.
To fix this issue, we identified the Informatica maplet and replaced the existing Transformation code
IIF(ISNULL(PROMISED_ON_DT), NULL, TO_INTEGER(TO_CHAR(PROMISED_ON_DT,’YYYYMMDD’)))
with
IIF(ISNULL(PROMISED_ON_DT), NULL, TO_INTEGER(IIF( SUBSTR(TO_CHAR(PROMISED_ON_DT,’YYYYMMDD’),1,2) =’00’, ’20’||SUBSTR(TO_CHAR(PROMISED_ON_DT,’YYYYMMDD’),3),TO_CHAR(PROMISED_ON_DT,’YYYYMMDD’) )))
This update has resolved the DAC task failing error.