SQL Server 2005 SSIS Oracle Import Problem

[TY4TheTip]

Hello All,
I am using the Import/Export Wizard of SSIS to import data from Oracle 9I to SQL Server 2005 SP3. I am using Microsoft OLE DB Provider for Oracle and Microsoft OLE DB Provider for SQL Server. Since I have had some truncation issues in the past, I am mapping Oracle Number to Numeric(38,16) In the SSIS Package. I am showing a discrepancy on some of the Oracle columns that are using the NUMBER data type when precision and scale is not specified. As an example, on one of the rows I imported a column called current_rate. Current_rate displays as 0.681728317630858 when selected in SQL Server Mangement Studio. The same row in Oracle displays the current_rate as .681728318 in both SQL Plus and Toad. I'm hoping someone with expertise in SSIS and Oracle can help me resolve this issue.

 

[Rhys666]

With SQL Server being the destination I don't think its an SSIS issue. It looks like the displayed value in SQL Plus/Toad is being rounded at 9 decimal places, whilst when viewing the value via Management Studio you're seeing it in full.

Haven't looked for a while but is there a setting in SQL Plus/Toad to set the maximum number of viewed/returned decimal places from a Number type?

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett