OLE DB Oracle Number Porblem

Question

Post reply

OLE DB Oracle Number Porblem

laittp

SSC Veteran

Points: 239
More actions
April 2, 2008 at 3:39 am

#121364

Hi Guys,
I'm pretty new to SSIS but I have been working on this problem all day and can't work out how to get around it.
I am pumping data out of SQL server into Oracle Via the Microsoft OLE DB for Oracle Connection.
Everything is going ok but the one column with my important data (which is a float in sql) is not inserting a value if the field has a decimal.
I think it has to do with the Number problem which Scott Barrett talks about here http://microsoftdw.blogspot.com/2005/11/okhow-do-i-get-data-out-of-oracle.html
so I tried his solution and set the external columns data type for that column to DT_R8 but it doesn't save my changes and just reverts back to DT_NUMERIC!
Any help would be great
Thanks
Patrick

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Set the ValidateExternalMetaData property to False.

However, you are better off using a CAST or CONVERT in your SQL statement and changing the column to another data type.

laittp SSC Veteran Points: 239 More actions · Answer 2

Thanks Michael I turned off ValidateExternalMetaData but my entries with a decimal place are stilling coming into oracle as nothing.

I'm not very familiar with MSSQL datatype's which one should i use? i just need double precision really.

But my thought is it might be something else as I am not getting anything in the lines with decimal's i thought if it was this problem it would at least put in everything before the decimal point.

thanks

Pat

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

I would use the NUMERIC data type and select a precision appropriate for your data. The FLOAT and REAL data types are approximate numerics and that may impact the Oracle client.

I would guess that the Oracle client version you are using is not quite happy with the MS OLEDB driver you are using and during the data type conversion it errors and turns the data into a NULL and then (as so many versions of the oracle client do with OLEDB drivers) it decided to replace NULL with empty string.

Switching to a DECMIAL data type or another numeric that is not an approximate could eliminate the issue. You could do this in SSIS, but it would probably be easiest to use CAST or CONVERT in your T-SQL query to get it to a better data type to begin with.