Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Using Transformations in Jobs no Data Changes

  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Default Using Transformations in Jobs no Data Changes

    Hello,

    we have a transformation which reads the data from one database connection and the writes the data to another database in two tables.
    If the transformation is executed in spoon seperate it is working fine.
    But if we use the transformation in a very simple Job (Start->MyTransformation->Success) some steps which write the data to the table are not executed.
    btw: we use global Variables for the database connections and some debug switches.

    Some ideas?

    Execution protocol as single transformation:
    Code:
    2012/07/16 10:20:24 - Spoon - Transformation opened.
    2012/07/16 10:20:24 - Spoon - Launching transformation [ST_BANK]...
    2012/07/16 10:20:24 - Spoon - Started the transformation execution.
    2012/07/16 10:20:24 - ST_BANK - Dispatching started for transformation [ST_BANK]
    2012/07/16 10:20:24 - Transformation metadata - Natural sort of steps executed in 0 ms (20 time previous steps calculated)
    2012/07/16 10:20:24 - Table Migrated Output.0 - Connected to database [LeaswareTARGET] (commit=1000)
    2012/07/16 10:20:25 - Mapping Table Output.0 - Connected to database [LeaswareTARGET] (commit=1000)
    2012/07/16 10:20:28 - Table input.0 - Finished reading query, closing connection.
    2012/07/16 10:20:28 - Table input.0 - Finished processing (I=28713, O=0, R=0, W=28713, U=0, E=0)
    2012/07/16 10:20:28 - Get Variables.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:28 - Database lookup.0 - Finished processing (I=29900, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:28 - BANK EXISTS.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:28 - Write to log.0 - Finished processing (I=0, O=0, R=28563, W=28563, U=0, E=0)
    2012/07/16 10:20:28 - AC IS_NOT_NEW.0 - Finished processing (I=0, O=0, R=28563, W=28563, U=0, E=0)
    2012/07/16 10:20:28 - Select Output.0 - Finished processing (I=0, O=0, R=150, W=150, U=0, E=0)
    2012/07/16 10:20:28 - ST_BANK_SEQ.0 - Finished processing (I=0, O=0, R=150, W=300, U=0, E=0)
    2012/07/16 10:20:28 - Select Mapping 2.0 - Finished processing (I=0, O=0, R=28563, W=28563, U=0, E=0)
    2012/07/16 10:20:28 - AC IS_NEW.0 - Finished processing (I=0, O=0, R=150, W=150, U=0, E=0)
    2012/07/16 10:20:28 - Select Mapping.0 - Finished processing (I=0, O=0, R=150, W=150, U=0, E=0)
    2012/07/16 10:20:29 - Write DATA.0 - Finished processing (I=0, O=0, R=150, W=150, U=0, E=0)
    2012/07/16 10:20:29 - Remove Variables.0 - Finished processing (I=0, O=0, R=150, W=150, U=0, E=0)
    2012/07/16 10:20:29 - Excel Output.0 - Finished processing (I=0, O=150, R=150, W=150, U=0, E=0)
    2012/07/16 10:20:29 - Add constants.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:29 - Mapping Table Excel.0 - Finished processing (I=0, O=28713, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:30 - Table Migrated Output.0 - Finished processing (I=0, O=150, R=150, W=150, U=0, E=0)
    2012/07/16 10:20:36 - Write Mapping.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:43 - Remove Variables 2.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:51 - Mapping Table Output.0 - Finished processing (I=0, O=28713, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:20:51 - ST_BANK - All transaction of database connection 'LeaswareSOURCE' where committed at the end of the transformation.
    2012/07/16 10:20:51 - ST_BANK - All transaction of database connection 'LeaswareTARGET' where committed at the end of the transformation.
    2012/07/16 10:20:51 - Spoon - The transformation has finished!!
    Execution protocol in a simple job:
    Code:
    2012/07/16 10:22:28 - Spoon - Starting job...
    2012/07/16 10:22:28 - Bank - Start of job execution
    2012/07/16 10:22:28 - Bank - Starting entry [ST_BANK]
    2012/07/16 10:22:28 - ST_BANK - Loading transformation from repository [ST_BANK] in directory [/Leasware Migration]
    2012/07/16 10:22:28 - ST_BANK - Dispatching started for transformation [ST_BANK]
    2012/07/16 10:22:28 - Table Migrated Output.0 - Connected to database [LeaswareTARGET] (commit=1000)
    2012/07/16 10:22:29 - Mapping Table Output.0 - Connected to database [LeaswareTARGET] (commit=1000)
    2012/07/16 10:22:32 - Table input.0 - Finished reading query, closing connection.
    2012/07/16 10:22:32 - Table input.0 - Finished processing (I=28713, O=0, R=0, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - Get Variables.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - Database lookup.0 - Finished processing (I=30050, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - BANK EXISTS.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - Write to log.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - AC IS_NOT_NEW.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - Select Mapping 2.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - Add constants.0 - Finished processing (I=0, O=0, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:32 - Write Mapping.0 - Finished processing (I=0, O=0, R=28713, W=0, U=0, E=0)
    2012/07/16 10:22:33 - Mapping Table Excel.0 - Finished processing (I=0, O=28713, R=28713, W=28713, U=0, E=0)
    2012/07/16 10:22:33 - ST_BANK - All transaction of database connection 'LeaswareSOURCE' where committed at the end of the transformation.
    2012/07/16 10:22:33 - Bank - Starting entry [Success]
    2012/07/16 10:22:33 - Bank - Finished job entry [Success] (result=[true])
    2012/07/16 10:22:33 - Bank - Finished job entry [ST_BANK] (result=[true])
    2012/07/16 10:22:33 - Bank - Job execution finished
    2012/07/16 10:22:33 - Spoon - Job has ended.
    2012/07/16 10:22:33 - ST_BANK - All transaction of database connection 'LeaswareTARGET' where committed at the end of the transformation.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    There are differences:
    • The step "Database lookup" has a different row count.
    • When running the job, the step "Select Output" has no input.

    I only would be alarmed, if these differences have no impact on the omission of certain steps.

    Do you observe the omission of steps, even when exactly the same data is used?
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Jul 2012
    Posts
    2

    Default

    Hi,

    the problem was with the scope of environment variables:
    We use some global variables from the environment like ${WRITE_MAPPING} and switch/case decisions to configure if the data has to be written in database or just in a Excel file - for development and debugging.
    If we use the transformation itself, the environment is passed to the transformation.
    If the transformation is used in a job, a "Set Variables" for the variables used in the transformation must bei executed in the job...
    otherwise the variables are not set ...

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.