none
SSIS convert Oracle number data type to SQL server integer RRS feed

  • Question

  • I have an SSIS, need to use merge join two data sources.

    One is from oracle, one is from SQL server, and the final destination is SQL server too.

    Oracle has an ID field which is data type Number (10,0) , and SQl server ID field is integer.

    I need to join the two. But the data type is miss matching.

    How can I convert the one in Oracle the same as integer, in SSIS it is DT_I4 (four-byte signed integer)


    Thanks


    SQLFriend

    Thursday, May 16, 2019 5:13 PM

Answers

  • Hi SQLFriend, 

    You can also try the Data Conversion transformation to convert DT_NUMERIC to DT_I4. 

    Note that if you use Derived Column, you should choose <add as new column>. And then you can use the new column in downstream components. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by msloy Friday, May 17, 2019 3:37 PM
    Friday, May 17, 2019 3:22 AM

All replies

  • Hi SPFriend,

    You can CAST Oracle's NUMERIC column as INTEGER:

    CAST function

    Along the following:

    SELECT CAST(columnName AS INTEGER)
    FROM tableName;
    Thursday, May 16, 2019 5:20 PM
  • I tried that, but in SSIS in the column property , it still is made into numeric [DT_NUMERIC]

    I want it to be  DT_I4 

    Thanks


    SQLFriend

    Thursday, May 16, 2019 5:38 PM
  • Hi SPFriend,

    What is your Oracle DB version?

    Please share your entire SQL statement.

    Thursday, May 16, 2019 6:03 PM
  • Hi SPFriend,

    It seems that Oracle always use NUMERIC data type even for INTEGER columns, just the precision is zero.

    In such case you can try to use SSIS Derived Column transformation.The following expression should work:

    (DT_I4)OracleColumn

    • Proposed as answer by Yang.Z Friday, May 17, 2019 3:15 AM
    Thursday, May 16, 2019 6:26 PM
  • Hi SQLFriend, 

    You can also try the Data Conversion transformation to convert DT_NUMERIC to DT_I4. 

    Note that if you use Derived Column, you should choose <add as new column>. And then you can use the new column in downstream components. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by msloy Friday, May 17, 2019 3:37 PM
    Friday, May 17, 2019 3:22 AM
  • Thanks, that was what I finally did. Use data conversion or derived column to explicitly convert it.



    SQLFriend

    Friday, May 17, 2019 3:37 PM