Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to run custom sql statment only once in Execute Sql Script step in transformation

  1. #1
    Join Date
    May 2007
    Posts
    9

    Question How to run custom sql statment only once in Execute Sql Script step in transformation

    I am facing a problem to update a table Transfer_History once after the succesfull data transformation in target table. the Transfer_History maintain the record of last date when last time transformation happened.

    I have 3 steps

    Input table - > insert/update -> execute sql script

    I have written the below sql stmt in execute sql script step

    ------------
    insert into transfer_history(data_transfer_date) values(getdate())
    -----------

    But this work only when i checked the "Execute for each row" property in this execute sql script step. In this case it insert the date in transfer_history table as much as the records is inserted from insert/update step means this execute for each row.

    But i want to execute the insert stmt in execute sql script step only once after the completion od insert/update step.

    If i unchecked the Execute for each row" property in execute sql script step then transformation raised an error like

    Execute SQL script 4 - Error Step is receiving info from other steps but the SQL is only executed once!

    can anyone help me in this?

  2. #2

    Default

    Hi Rakes_humar. Have you solved this problem? I'm facing the same issue. In my case I want to add a column in a table and it can run only once, otherwise I'll get a 'duplicate column' error message.

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

    Default

    If your SQL statement is supposed to be executed only once, you should use the SQL job entry.
    If you want to stay with the transformation, you must reduce the rowset to a single row - a Group By step comes to mind.
    So long, and thanks for all the fish.

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.