Timestamps Oracle OCI to OLE/DB??

zbethem · Post by **zbethem** » Mon May 15, 2006 7:18 pm

Hi all. I'm finding some interesting behavior around timestamps. I've defined columns on both the Oracle and SQLServer side as 'Timestamp'.

My job is as follows:

Code: Select all

OCI-->Trans-->OLE/DB

What I'm seeing is as follows:
1. null dates in Oracle are trying to be written as '0000/00/00 00:00:00' in SQLServer
2. inconsistency in passing the actual date value; oracle values of '2006-05-12 00:00' are mysteriously translated to '05/11/2006 00:00:00'

Can anyone out there recommend any techniques to use regarding this? I've tried converting to the internal Date format, but that didn't seem to help.

Thanks in advance.

Loving the Micro$oft HRESULT errors.. makes everything so obvious...

ray.wurlod · Post by **ray.wurlod** » Mon May 15, 2006 8:36 pm

For debugging purposes, put a Sequential File stage between the Transformer stage and the OLEDB stage. What format gets written into the text file? This should also isolate where the problem is occurring.

zbethem · Post by **zbethem** » Tue May 16, 2006 10:02 am

Thanks Ray. I've resolved the issue #2, it was me javascript:emoticon(':roll:')
Rolling Eyes concentrating on the HRESULT error versus looking at the data.

That being said, however, the first issue is still around. Null Oracle date columns are still trying to be written as '0000/00/00 00:00:00'. I'm seeing this in the job's log in Director. The sequential file looks like the Oracle side, Null for the date column.

For reference, the job now is:

Code: Select all

OCI-->Trans-->SeqFile-->Trans-->OLE/DB

Is there an easier way to lookup these HRESULT errors that I don't know about? They seem to be cryptic versus those nice ORA- or DB2 error codes.

Thanks again.

zbethem · Post by **zbethem** » Tue May 16, 2006 11:36 am

Just wanted to update the forum...

So things seem to be working fine now. I was getting tripped up in the HRESULT error message when the job executed. I've since found out that the error was being caused by data issues in the source. For some reason, the error message reporting the data values was showing null dates as '00/00/0000', but things are inserted properly now that the data issues are resolved.

Thanks for your help... again.