Data type changing on SSIS extract from Oracle

Question

Data type changing on SSIS extract from Oracle

Scott-144766

SSCarpal Tunnel

Points: 4333
More actions
July 31, 2008 at 9:12 am

#195158

SQL 2005 SP2, Oracle 8i
I'm extracting currency data from Oracle, but when I look at the data in SQL Server the values have been converted to integers so I've lost all the pence (or cents if I was in the US). The field I'm importing into is decimal(10,2). I've worked around it by multiplying by 100 in my Oracle extract query and using a derived column transform to divide again, but it's a pretty ugly kludge. I'm using Microsoft OLEDB Provider for Oracle (MSDORA.1). I've doublechecked my source data and it definitely contains data after the decimal point. Can anyone point me in the right direction to fix this?
Thanks
Scott
--
Scott

Viewing 9 posts - 1 through 8 (of 8 total)

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

hodgy SSCertifiable Points: 5906 More actions · Answer 1

check the metadata on your source transformation and see how the column is being pulled across. you may need to do an explicit conversion in your source sql

tom

Life: it twists and turns like a twisty turny thing

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 2

The metadata says DT_NUMERIC (38,0) for the column, even when I do an explicit cast to decimal(10,2) in the source query. I can't see a way to edit the metadata.

Scott

--
Scott

hodgy SSCertifiable Points: 5906 More actions · Answer 3

i usually find the easiest way is to delete the original source and add a brand new one with the modified sql (the code with the explicit cast in it). the metadata should then reflect your sql.

Life: it twists and turns like a twisty turny thing

Scott-773970 Newbie Points: 8 More actions · Answer 4

I've just tried that with the same outcome as before. My source query contains CAST(AMOUNT_DUE_ORIGINAL AS DECIMAL(10,2)) and this is showing as DT_NUMERIC (38,0) in the metadata.

hodgy SSCertifiable Points: 5906 More actions · Answer 5

what happens if you set the ValidateExternalMetadata to false?

Life: it twists and turns like a twisty turny thing

Scott-773970 Newbie Points: 8 More actions · Answer 6

Same as before. Just to confirm what I'm doing:

in the data flow task, delete the existing OLE DB source

create new source pointing at the connection manager for Oracle

set data access mode to SQL command and paste in my query

set ValidateExternalMetadata to False

then connect OLE DB source to OLE DB destination

doubleclick on data flow path and click Metadata on the left

still says DT_NUMERIC (38,0)

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

The 10g oracle client is not 100% OLEDB compliant. I am sure it is not the only client version with compliance issues. It has caused the same issue you are describing and also, at times will completely reject fields regardless of casting them indicating that the scale and precision is outside the acceptable range.

Both the MS OLEDB driver and the Oracle OLEDB driver gave me the same results.

To deal with it, I usually convert decimal numbers to strings in my query and then convert them back to decimals in the SSIS package.

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 8

Looks like I'll carry on kludging then. Thanks for the help.

Scott

--
Scott