SSIS Cannot Convert ORACLE datatype

Question

SSIS Cannot Convert ORACLE datatype

ryan.reinaldi

Ten Centuries

Points: 1057
More actions
September 8, 2009 at 9:48 pm

#137991

Hi guys its me ryan, I have some question regarding SSIS transfer from Oracle to SQL Server 2005.
I have a data in Oracle like this for example :
ID INV_ID SEGMENT_O CON_SEGMENT DESCRIPTION TON
----------------------------------------------------------------------
610 129087 190 188891 ATK 4.89
610 129087 190 188892 BRIG 0.5698
610 129087 190 188892 HAD 0.213
I want to transfer that data to SQL Server, when it fianlly transferred to SQL Server the data is went weird, instead being the same form as I mention above it turns out to be different, it went like this:
ID INV_ID SEGMENT_O CON_SEGMENT DESCRIPTION TON
----------------------------------------------------------------------
610 129087 190 188891 ATK 5
610 129087 190 188892 BRIG 0
610 129087 190 188892 HAD 0
The TON column is not the same in the Oracle, the TON column came from a calculation in Oracle query.
Can you guys help me what went wrong ? I already use datatype conversion in SSIS but it just wont work, please , I really need the answer. Thanks
September 9, 2009 at 1:04 am

This was removed by the editor as SPAM
September 9, 2009 at 11:20 pm

This was removed by the editor as SPAM
September 9, 2009 at 11:34 pm

This was removed by the editor as SPAM
September 10, 2009 at 1:48 am

This was removed by the editor as SPAM

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

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

ryan.reinaldi Ten Centuries Points: 1057 More actions · Answer 1

Hi stewartc, thanks for the reply. I already check the data type both from the oracle and sql server. They are the same, both of them are NUMERIC(38,0) but still the problem still occurs, I don;t know why. Maybe you or other people have any other suggestions.

Thanks,

Ryan

Lowell SSC Guru Points: 323504 More actions · Answer 2

numeric (38,0) would be 38 significant digits, NONE to the right of the decimal place.

you want numeric(38,6) for example...38 digits, so that would be 32 to the left of the decimal, 6 to the right.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ryan.reinaldi Ten Centuries Points: 1057 More actions · Answer 3

hi thanks for the reply, I guess you guys were right. I already try to change the precision and scale options in the advanced editor, but turns out to be it gives me error in the data flow source. By the way, could you give any example for CAST query on oracle, beacuse I'm not too good in oracle, I got the query from someone else, I just use it hehehe. Sorry if I'm being a li'l bit bothering.

Thanks

ryan.reinaldi Ten Centuries Points: 1057 More actions · Answer 4

hi thanks for the reply, I guess you guys were right. I already try to change the precision and scale options in the advanced editor, but turns out to be it gives me error in the data flow source. By the way, could you give any example for CAST query on oracle, beacuse I'm not too good in oracle, I got the query from someone else, I just use it hehehe. Sorry if I'm being a li'l bit bothering.

Thanks

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 5

vishal.gamji

SSCertifiable

Points: 6126

September 10, 2009 at 7:30 am

#1051707

Did u try using the TO_CHAR from the oracle query ??