Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS datatype mapping of Oracle Number columns

คำถาม SSIS datatype mapping of Oracle Number columns

  • Friday, February 01, 2013 12:57 PM
     
     
    Sign In to Vote
    0

    Hello all,

    I am struggling with an issue regarding data transfer from Oracle (Release 11.2.0.1.0) to SQL Server (2008R2) via SSIS.

    The Oracle source table has a column, lets call it num1, defined as datatype NUMBER in Oracle, with neither Precision nor Scale defined. According to Microsoft's recommendations for datatype mapping between Oracle and SQL Server I have declared the corresponding column in SQL Server as a float.

    For the Oracle source data I am using an ADO NET Source SSIS dataflow component. When I connect it to Oracle and specify the table containing the num1 column and then look at the Output properties of this column, to my surprise SSIS maps/converts this column into DT_NUMERIC(38,4). What this means, if I understand correctly, is that SSIS limits the output of the column value to the destination to 4 decimals and if the value in Oracle contains more than 4 decimals, a rounding of the value will occur. I have observed this behavior using these two .NET providers:

    - System.Data.OracleClient (The Microsoft one that ships with .NET but has been stated as "deprecated")
    - Oracle.DataAccess.Client (The one provided by Oracle in ODAC)

    Using either of those two providers, the value "1.12345" stored in a NUMBER-column in Oracle will be rounded already in the data flow between Oracle and SSIS, and stored in the corresponding SQL Server column as "1.1235". As I described earlier I have declared the SQL Server column as a float, but just to eliminate the possibility that the rounding of the value should occur in SQL Server, I have also tried with the SQL column being declared as a NUMERIC(38,10). The same result is achieved in both cases which indicate that the rounding is done before the value is stored in SQL Server.

    One interesting thing is that when I tried the same scenario using an OLE DB Source component and using the MSDAORA (Microsoft OLE DB provider for Oracle) instead, SSIS mapped the very same column as a "DT_R8", which, according to this nice datatype chart provided by Devin Knight, corresponds to a float datatype, which seems to be more in line with Microsoft's recommendations on the link above.

    I wonder if anyone can explain to me why the two .NET providers from Microsoft and Oracle maps Oracle NUMBER-columns (without precision and scale defined) to NUMERIC(38,4) and thereby effectively limits the output precision of the column value to 4 decimals?

    Also, can anyone see a workaround for this if we want to continue using the Oracle .NET provider (as recommended by Microsoft) and at the same time avoid rounding of the value in SSIS? We cannot change the Oracle column definitions, those are out of our control. Do we have enter the world of CONVERT's in the select-statement against Oracle? I would rather avoid this but if it's the only way to go to preserve the precision of the value from Oracle, we might as well have to look into this.

    Thankful for any input,
    Martin


    • Edited by Martin_Ar Friday, February 01, 2013 12:58 PM
    •  
     

All Replies

  • Friday, February 01, 2013 2:10 PM
     
     
    Sign In to Vote
    0

    Hi Martin,

    I think you can explicitly convert it to float. Go to ADO NET src advanced properties --> input-output properties ---> ADo NET src o/p ---> O/p columns.

    Thanks,

    Thanks, hsbal

     
  • Tuesday, February 12, 2013 4:38 PM
     
     
    Sign In to Vote
    0

    Thanks for your reply Harry,

    I am afraid your suggestion do not work. Actually, I had already tried your suggestion before writing my post to this forum. Obviously, it is not possible to change the data type of output columns from an SSIS ADO NET Source, unless they are already defined as either DT_WSTR or DT_NTEXT.

    When I try to do this on a column that is specified in Oracle as a plain NUMBER (without precision nor scale defined), and which has already been automatically converted by SSIS to a DT_NUMERIC(38,4), I get the following error message:

    Error at Extract Data [ADO NET Source [1]]: The data type of output columns on the component "ADO NET Source" (1) cannot be changed, except for DT_WSTR and DT_NTEXT columns.

    Martin