Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: SQL INSERT Primary Key and Allow Null

  1. #1
    Join Date
    Sep 2011
    Posts
    171

    Default SQL INSERT Primary Key and Allow Null

    Hello Everyone,

    I am transferring data from Excel to MS SQL database.

    There are two columns (FundName and Date) in SQL, which give me problem. If I choose FundName and Date as primary key (both them) then transfer does not go through (see below). If I remove primary key from the both columns and allow null then transfer goes through perfectly


    As I can see from below there is a complain about date format.

    What am I doing wrong!?

    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Because of an error, this step can't continue:
    2011/09/23 14:59:12 - Dummy (do nothing).0 - Finished processing (I=0, O=0, R=504, W=504, U=0, E=0)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleValueException:
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Exchange Ticker String : couldn't convert string [CONCOR] to a date using format [yyyy/MM/dd HH:mm:ss.SSS]
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unparseable date: "CONCOR"
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertStringToDate(ValueMeta.java:609)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.getDate(ValueMeta.java:1660)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertData(ValueMeta.java:3061)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertBinaryStringToNativeType(ValueMeta.java:1124)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.getString(ValueMeta.java:1279)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.RowMeta.getString(RowMeta.java:184)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.RowMeta.getString(RowMeta.java:605)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:434)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:117)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.text.ParseException: Unparseable date: "CONCOR"
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.text.DateFormat.parse(Unknown Source)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertStringToDate(ValueMeta.java:604)
    2011/09/23 14:59:12 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 10 more
    2011/09/23 14:59:12 - Table output.0 - Finished processing (I=0, O=1, R=2, W=1, U=0, E=1)
    2011/09/23 14:59:12 - Spoon - The transformation has finished!!
    2011/09/23 14:59:12 - TransferCashPositionsForSA - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/09/23 14:59:12 - TransferCashPositionsForSA - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/09/23 14:59:12 - TransferCashPositionsForSA - TransferCashPositionsForSA
    2011/09/23 14:59:12 - TransferCashPositionsForSA - TransferCashPositionsForSA

    <hop> <from>Filter rows</from><to>Table output</to><enabled>Y</enabled> </hop>
    <hop> <from>Filter rows</from><to>Dummy (do nothing)</to><enabled>Y</enabled> </hop>
    </order>
    <notepads>
    </notepads>
    </transformation-steps>

  2. #2
    Join Date
    Jun 2007
    Posts
    260

    Default

    Either your data, or your data mapping is not correct.

    It appears you have a value 'CONCOR' in the Date field, which is obviously not a correct date. Java therefore complains in trying to convert the value from one type to another.

    Try and fix that first.

  3. #3
    Join Date
    Sep 2011
    Posts
    171

    Default

    I checked there is no 'CONCOR' (at least I could no find)

    Also, how come everything goes through when FundName and Date are not primary keys?

  4. #4
    Join Date
    Jun 2007
    Posts
    260

    Default

    Strange then where it gets the string from.

    Create another step, and text file output with 2 string fields (FundName and Date), and attach that to the error handling step of the table output (right click on the table output step and choose error handling). In that way you will see what data cannot be written to the database.

  5. #5
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you for the suggestion. I found the error: I was simply pasting duplicate values.

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.