Hitachi Vantara Pentaho Community Forums
Results 1 to 19 of 19

Thread: Running simple KTR from BI server

  1. #1

    Question Running simple KTR from BI server

    Hi all,

    i made a simple transformation with Spoon, which works fine when running in Spoon.

    The transformation reads some data from Excel and from a SQL table, merges this data and writes the merged data in another SQL table.

    When i start this transformation from the BI server (biserver-ce-3.0.0-STABLE) i get these errors:

    Unable to find kettle engine jar file to set build date. (ignored)
    18:00:01,236 ERROR [0] Table output.0 - Unexpected batch update error committing
    the database connection.
    18:00:01,236 ERROR [0] Table output.0 - org.pentaho.di.core.exception.KettleData
    baseBatchException:
    Error updating batch
    Ung³ltiger Spaltenname 'Kunde'.

    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:14
    02)
    at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutpu
    t.java:565)
    at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2706)
    at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.ja
    va:634)
    Caused by: java.sql.BatchUpdateException: Ung³ltiger Spaltenname 'Kunde'.
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(
    Unknown Source)
    at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:13
    86)
    ... 3 more


    Here is my xaction file:

    <?xml version="1.0" encoding="UTF-8"?>
    <action-sequence>
    <name>SampleTransformation.xaction</name>
    <title>%title</title>
    <version>1</version>
    <logging-level>debug</logging-level>
    <documentation>
    <author>Gretchen Moran</author>
    <description>%description</description>
    <help>%help</help>
    <result-type>rule</result-type>
    <icon>HelloETL.png</icon>
    </documentation>

    <inputs/>

    <outputs>
    <rule-result type="result-set"/>
    </outputs>

    <resources>
    <transformation-file>
    <solution-file>
    <location>ETL.ktr</location>
    <mime-type>text/xml</mime-type>
    </solution-file>
    </transformation-file>
    </resources>

    <actions>
    <action-definition>
    <component-name>KettleComponent</component-name>
    <action-type>Execute Kettle Transformation</action-type>
    <action-inputs/>
    <action-resources>
    <transformation-file type="resource"/>
    </action-resources>
    <action-outputs>
    <transformation-output type="result-set" mapping="rule-result"/>
    </action-outputs>
    <component-definition>
    <importstep>XML Output</importstep>
    </component-definition>
    </action-definition>

    </actions>
    </action-sequence>


    What am i doing wrong here?

    Cheers,
    Andreas

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Look at the samples in the solutions folder.

    Code:
    <resources> 
        <transformation-file> 
          <solution-file> 
            <location>ETL.ktr</location>  
            <mime-type>text/xml</mime-type> 
          </solution-file> 
        </transformation-file> 
      </resources>
    You need to specify the filename, not the name or location (repository related).

    Matt

  3. #3

    Default

    Hi Matt,

    i looked at the samples and the sample etl xaction files are just the same as mine, so i don't think the filename is the problem.

    I looked in my ETL.ktr file and found this line:

    <attribute><code>CUSTOM_URL</code><attribute>jdbc:sqlserver://localhost\SQL2005;databasename=Demo;responseBuffering=adaptive</attribute></attribute>

    I changed it to this line

    <attribute><code>CUSTOM_URL</code><attribute>jdbc:sqlserver:localhost\SQL2005;databasename=Demo;responseBuffering=adaptive</attribute></attribute>

    and now it seems that the transformation is running.

    But now i get these errors in the BI server:

    Failed

    Fehler: Kettle.ERROR_0008 - Beim Ausführen der Transformation ist ein Fehler aufgetreten - org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0011 - Fehler beim Vorbereiten der Transformation - org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0011 - Fehler beim Vorbereiten der Transformation (org.pentaho.platform.plugin.action.kettle.KettleComponent)

    Debug: steel-wheels/samples/ETLTransformation.xaction wird ausgeführt (org.pentaho.platform.engine.services.solution.SolutionEngine)
    Debug: Hole Runtime Context und Daten. (org.pentaho.platform.engine.services.solution.SolutionEngine)
    Debug: LAde Actionsequenz Definitionsdatei (org.pentaho.platform.engine.services.solution.SolutionEngine)
    Debug: audit: instanceId=fc1c5cda-68b5-11de-8088-196572251911, objectId=org.pentaho.platform.engine.services.runtime.RuntimeContext, messageType=action_sequence_start (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: validateComponent validiert die Komponenten KettleComponent (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Validierung der Komponente für ETLTransformation.xaction (org.pentaho.platform.plugin.action.kettle.KettleComponent)
    Debug: Aktionsequenz wird ausgefürht. (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Aktionsdefinition ausführen: Iteration 0 (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: audit: instanceId=fc1c5cda-68b5-11de-8088-196572251911, objectId=KettleComponent, messageType=component_execution_started (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Pre-audit ausführen. (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Komponenten Log-Level wird auf DEBUG gesetzt. (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Komponente wird initialisiert. (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: executeComponent startet die geprüfte Ausführung. (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: Validierung ausführen=true (org.pentaho.platform.plugin.action.kettle.KettleComponent)
    Debug: Wird ausgeführt... (org.pentaho.platform.plugin.action.kettle.KettleComponent)
    Debug: Transformation wird vorbereitet (org.pentaho.platform.plugin.action.kettle.KettleComponent)
    Fehler: Kettle.ERROR_0008 - Beim Ausführen der Transformation ist ein Fehler aufgetreten - org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0011 - Fehler beim Vorbereiten der Transformation - org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0011 - Fehler beim Vorbereiten der Transformation (org.pentaho.platform.plugin.action.kettle.KettleComponent)
    Debug: executeComponent schliest geprüftes Ausführen ab. (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Fehler: RuntimeContext.ERROR_0012 - Die ActionDefinition für KettleComponent wurde nicht erfolgreich ausgefürht. (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Debug: audit: instanceId=fc1c5cda-68b5-11de-8088-196572251911, objectId=org.pentaho.platform.engine.services.runtime.RuntimeContext, messageType=action_sequence_failed (org.pentaho.platform.engine.services.runtime.RuntimeContext)
    Fehler: SolutionEngine.ERROR_0007 - Fehler beim Ausführen der Actionsequenz (org.pentaho.platform.engine.services.solution.SolutionEngine)

    Server Version: Pentaho Platform Engine Core 3.0.0-STABLE.37736

    What's wrong now? And why is my transformation running fine in Spoon but not in BI server?

  4. #4

    Unhappy

    I did several more testings and i think that i have a connection problem when running the KTR file from the BI server that i don't have when running it from Spoon.

    I use sqljdbc.jar driver for MS SQL 2005 connections (\tomcat\webapps\pentaho\WEB-INF\lib). In the Administrator console i have defined a data source that connects to my SQL 2005 database.

    Driver and data source work fine for reports that i am running from the BI server already.

    So how should the connection section in the KTR file be defined for using my pre-defined SQL 2005 data source for my ETL transformation (read from SQL 2005 table, read from Excel, merge both data, write back to another SQL 2005 table)?

    If i can't use this pre-defined data source: where should i place the sqljdbc.jar and how should i define the SQL 2005 connection in the KTR file for this case?

  5. #5

    Default

    Problem solved!

    There was something wrong in my table output step:

    I had to add the field 'Kunde' to my sql output table, although it isn't really necessary for my transformation output (i don't write data to such a field).

    Spoon doesn't notify me about this missing field and my transformation runs fine in Spoon.

    But the BI server kettle component seems to see an error here, don't know why at the moment.

    After adding this field to my output table the transformation runs fine in the BI server too.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    You are probably mixing Kettle versions.
    The next version of the BI Server will have Kettle v3.2.
    The latest stable is at 3.1.x.

  7. #7

    Default

    I am using

    biserver-ce-3.0.0-STABLE
    Internal.Kettle.Version3.1.2
    Internal.Kettle.Build.Version
    10064
    Internal.Kettle.Build.Date2009/01/26 14:14:30

    and

    pdi-ce-3.2.0-stable.

    Is this a wrong combination?

  8. #8

    Default

    well quite obviously PDI 3.2.0 != PDI 3.1.2 and there has been a lot of changes to output stuff, field mapping etc.
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  9. #9

    Default

    Quote Originally Posted by bugg_tb View Post
    well quite obviously PDI 3.2.0 != PDI 3.1.2 and there has been a lot of changes to output stuff, field mapping etc.
    That could explain my problems.

    So i need PDI 3.1.0 stable in combination with BI Server 3.0.0 stable?

    And why is PDI 3.2.0 listed with BI Server 3.0.0 on the "Getting Started Downloads" page then? (http://wiki.pentaho.com/display/COM/...rted+Downloads)

  10. #10

    Default

    Quote Originally Posted by akrde View Post
    And why is PDI 3.2.0 listed with BI Server 3.0.0 on the "Getting Started Downloads" page then? (http://wiki.pentaho.com/display/COM/...rted+Downloads)
    That would be, because its the most current version.

    You can downgrade, or you can upgrade the libs in the biserver
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  11. #11

    Default

    Quote Originally Posted by bugg_tb View Post
    That would be, because its the most current version.

    You can downgrade, or you can upgrade the libs in the biserver
    All this is not very obvious to new users, isn't it?

    So when using BI server 3.0.0 stable:

    Which (stable) versions of

    PDI
    Design Studio
    Report Designer
    Metadata Editor
    Schema Workbench

    must be used to create compatible xml files to this server version?
    Last edited by akrde; 07-06-2009 at 07:38 AM.

  12. #12
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    I already gave you the answer for PDI.

  13. #13

    Default

    Quote Originally Posted by MattCasters View Post
    I already gave you the answer for PDI.
    Yes, that's right.

    So i will use PDI 3.1.0 stable instead of PDI 3.2.0 stable in combination with BI server 3.0.0 stable from now on.

    Thank you Matt, your and all the others help is very appreciated!

    Cheers,
    Andreas

  14. #14

    Default

    Quote Originally Posted by akrde View Post
    So when using BI server 3.0.0 stable:

    Which (stable) versions of

    Design Studio
    Report Designer
    Metadata Editor
    Schema Workbench

    must be used to create compatible xml files to this server version?
    ...anyone?

  15. #15
    pstoellberger Guest

    Default

    Design Studio, Report Designer, Metadata Editor come with the same version numbers as the biserver

    so for all of them it would be: 3.0.0-RC2

    schema must match the mondrian version on the biserver. i think that would be still 3.0.4

  16. #16

    Default

    Quote Originally Posted by pstoellberger View Post
    Design Studio, Report Designer, Metadata Editor come with the same version numbers as the biserver

    so for all of them it would be: 3.0.0-RC2

    schema must match the mondrian version on the biserver. i think that would be still 3.0.4
    Thanks!

    But all this is still quite confusing, even for new users, because when looking on http://wiki.pentaho.com/display/COM/...rted+Downloads a new user never would know that (s)he can't combine i.e. PDI 3.2.0 or Schema 3.1.1 with BI Server 3.0.0.

    A page, that shows new users which tool versions are combinable with BI Server xxx is missing here...

  17. #17

    Default

    Fair point akrde I'll setup a wiki page

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  18. #18

    Default

    Sorry misread your post
    That already exists, I'll ask Doug if he can tweak it a bit.

    Tom
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  19. #19

    Default

    Quote Originally Posted by bugg_tb View Post
    Sorry misread your post
    That already exists, I'll ask Doug if he can tweak it a bit.

    Tom
    That's great, thank you.

    Could you please keep us informed in this thread about the progress of this issue?

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.