none
Converting an Oracle Number data type to SQL Server Numeric data type

    Question

  • Hi there;

    I have a vendor application database that is hosted on Oracle and one of the fields I need to extract has a data type of number (No scale, no precision - Just NUMBER).

    When I do a preview of my SQL Query inside the OLE DB Source Data Flow Source, I see all of the data.

    When I write to SQL Server (Field defined as numeric (18,8)) I #1 don't get the precision (all 0s) and #2, rows that have small values (no units) show up as all 0.000s.

    I've tried to use a Data Conversion transform with no success and I also tried to CAST it using a derived Column again with no success. I tried to CAST it to a String in my Oracle Query using To_String() but then when I try to convert it back to a numeric in SSIS I get an error stating that I overflowed the data type.

    This field shows work effort in hours and fraction of hours so I can't have 0s as effort but I can have seconds and minutes as fractions of an hour.

    Any ideas out there? I'm getting Oracle-frustrated.

    Thanks

    Ron...
    Wednesday, May 20, 2009 4:31 PM

Answers

  • The issue can be circumvented by doing a CAST directly in the Oracle SQL to a known scope.


    Ron...
    • Marked as answer by RonInOttawa Thursday, July 23, 2009 3:53 PM
    Thursday, July 23, 2009 3:53 PM

All replies

  • Which version of SSIS are you using? 2005/2008/x64/x86?

    Try specifying a precision for NUMBER - that was the workaround for this issue in 2005, and that has been fixed in 2008.

    check the following :- http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282497

    • Proposed as answer by Josh Ashwood Thursday, May 21, 2009 4:43 AM
    • Unproposed as answer by RonInOttawa Thursday, May 21, 2009 10:16 AM
    Thursday, May 21, 2009 4:43 AM
  • Thanks for the feedback Johs - Unfortunately this is a vendor-supplied database and we can't go in and specify the precision to the number data type. I need to be able to convert it in SSIS.


    Ron...
    Thursday, May 21, 2009 10:19 AM
  • The issue can be circumvented by doing a CAST directly in the Oracle SQL to a known scope.


    Ron...
    • Marked as answer by RonInOttawa Thursday, July 23, 2009 3:53 PM
    Thursday, July 23, 2009 3:53 PM