DSXchange

Hi,

I have src cols having dates as 'YYMMDD' as Varchar2(6) in an Oracle table. I am trying to convert these using

StringToTimestamp(lnk.ColName : ' 00:00:00',"%2000yy%mm%dd %hh:%nn:%ss") (Defaulting Century as 2000) to write to another Oracle table. (Proper Null handling done)

We have 24 such date conversion cols and 4,75,000 records. It is loading 3,50,000 records and then I get the error and the job aborts
"ORC_StgRefIBSDDNS,1: Fatal Error: Invalid Julian day"

I have narrowed my records down to around 30,000 recs from within which this is failing.

Any tecnique whereby I can have DS to log the record where this is failing rather than manually checking all these 30K recs for 24 columns?

Thanks,
NV

Before it aborts it throws this warning

APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_string data may have been lost

Would using the IsValid() function be an option for you?

Since the Src Col data (dates) is in String format to be converted to Timestamp for target table, I guess it will not help to use IsValid() function

Better to profile the data in advance rather than reacting to surprises at the end. Usually these data problems are obvious... all zeros, all spaces, all nines, etc.

No need for a dedicated profiling tool... a simple parallel job with an aggregator is all you need... you've already identified the source column that needs profiling and a set of records to look at.

Mike

Resolved..

I designed a seperate job and sorted it on a Key col and then loaded it to the Target oracle table..all this was done in Sequential Mode and I set the Oracle Row Commit to 1..this inserted all the records till the one which was the Culprit.. Then I went back to the Src Table and sorted it on the same key..and this way I got the record prior to the Bad one.....

One of the Date cols in Src table had data '070931' and hence was failing the conversion as Sep should have 30 and not 31 days!!

So you have severely castigated the provider of the data? Such feedback is vital in improving data quality.

You want him to severely cut off their... oh sorry, castigate. Never mind.