none
Oracle --> SSIS --> SQL Server RRS feed

  • Question

  • Hi,

    I have a NUMBER (5) Oracle source type and NUMERIC (5) SQL Server target datatype.

    The SSIS's Oracle Connector I am using is automatically taking Source's  NUMBER (5) as four-byte signed integer [DT_14] based on below blog from Attunity.

    http://forums.attunity.com/forums/microsoft-ssis-oracle-connector/mapping-oracle-number-data-types-ssis-1326.html

    Can I declare all my SSIS's internal types for source's NUMBER (5) as four-byte signed integer [DT_14] and expect it be compatible with my SQL Server target's NUMERIC (5)?

    I have a scenario where my MERGE JOIN is not working as expected, and am suspecting datatype incompatibility as the cause of this problem?   I have made sure my sort orders are taken care, both in ORDER clause and also in SortKeyPositions......So, thought of seeking your opinion on this - thanks....

    Tuesday, October 4, 2016 10:50 AM

Answers

All replies

  • Hi,

    No, number accepts a fractional portion whereas DT_I4 is an integer which does not.

    You need to override the conversion to use DT_REAL to match on the precision. 

    Attunity shouldn't be converting like you said, this is a loss of data.


    Arthur

    MyBlog


    Twitter

    Tuesday, October 4, 2016 1:33 PM
    Moderator
  • thanks for the reply.

    At my source, the datatype has a precision of (5,0).  So, no fractional part.  May be that is why Attunity is making its own decision to convert to four-byte signed integer [DT_14] ?

    Next,   is  NUMBER (5,0) --->   DT_14 -->  NUMERIC (5,0)    a valid combination from Oracle ---> SSIS ---> SQL Server?

    thank you.

    Tuesday, October 4, 2016 1:42 PM
  • >NUMBER (5,0) --->   DT_14 -->  NUMERIC (5,0)    a valid combination from Oracle ---> SSIS ---> SQL Server?

    Yes.  The set of numbers represented by NUMBER(5,0) is a subset of the 4-byte integers. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Seif Wang Wednesday, October 5, 2016 1:58 AM
    • Marked as answer by Eric__ZhangModerator Wednesday, October 19, 2016 9:15 AM
    Tuesday, October 4, 2016 1:53 PM
  • Yes, do as you said

    NUMBER (5,0) --->   DT_14 -->  NUMERIC (5,0)

    so it is a match on both ends and it should work.


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Seif Wang Wednesday, October 5, 2016 1:59 AM
    Tuesday, October 4, 2016 3:58 PM
    Moderator