Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Error converting data type nvarchar to decimal - Oracle to SQL Server

  1. #1
    Join Date
    Mar 2017

    Default Error converting data type nvarchar to decimal - Oracle to SQL Server

    I'm attempting to convert data tables from an Oracle 11g database to a SQL Server 2012 data warehouse. There are Oracle Number fields which have decimals with a scale of 40 digits to the right of the decimal place. Therefore I'm using Float(53) fields in SQL server.

    I can see that the Table Input is pulling in the Number data as a BigNumber. When I attempt to connect to the Table Output and run the transformation I'm getting the above listed error. There are no string variables involved so I don't understand what is happening. I inserted a Select Values step and attempted to again specify the field type, but that didn't make any difference.

    I'm using the community version of Kettle 4.4.0. I have an oracle 11g client on the PC I'm using for the transformation. I'm using JDBC connections with the most recent jar files I could find. (ojdbc6_g, sqljdbc42) Java 8 update 111 is installed on the PC.

    If you have any thoughts regarding what's happening and how to fix it I would be very grateful.


  2. #2
    Join Date
    Mar 2017

    Default Revised question

    I've made some progress regarding this issue on my own. Currently I'm working with loading data into a SQL Decimal type instead of a Float because I found out that was not the appropriate type to use for a financial system.

    I've been able to load up to 17 decimal points by using the Pentaho Number data type rather than the BigNumber data type. Every time I use the BigNumber data type I end up with the original 'Error converting data type nvarchar to decimal' error. I found out that Pentaho converts all numbers to strings in order to use the masking formats which accounts for the nvarchar message.

    Does anybody have a way around this issue to enable loading additional decimal points to a SQL Server database?


  3. #3
    Join Date
    Apr 2008


    Perhaps post a simplified version of what it is you are trying to do, and we can provide input.
    With what you have provided, we're going to be guessing at best.

    Table Input -> Table Output

    OraNum as valueToMove

    valueToMove mapped to valueToMove
    Schema: DestinationSchema Table: DestinationTable

    Attaching the KTR of this sample is best.

  4. #4
    Join Date
    Mar 2017

    Default Further clarification

    I attached a simple version of the ktr.

    Using Pentaho Community version 4.4 on PC with Java 8 update 111

    Table Input - JDBC connection: ojdbc6_g.jar
    (Retrieving Oracle Number variable with 40 decimal points)

    Select Values - Select & Alter: MPO_TRANSACTION_FACTOR
    Meta-data: Fieldname : MPO_TRANSACTION_FACTOR
    Type : BigNumber
    Length : 38
    Precision : 37
    Format : #.#
    Decimal : .

    Table Output: Connection JDBC (sqljdbc42)
    Mapping: TRANSACTION_FACTOR to MPO_TRANSACTION_FACTOR in SQL Server database (Decimal (38,37))

    No matter what size I set the BigNumber to, I get the following error result:
    "Error converting data type nvarchar to decimal"

    If I change the BigNumber to Number Length 20 Precision 17 I successfully get 17 decimals.
    Attached Files Attached Files
    Last edited by rdykstra53; 03-14-2017 at 04:53 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.