Pentaho Transformation "Execute SQL Statements" vs "Table Input" step

Question

I am new to PDI/Kettle.I need to execute simple SQL select similar to "SELECT EMPID FROM Employee" and need to write the output to "Microsoft Excel Output" as part of reports generation.

1. When I write this query in "Execute SQL Statement" step under "SQL Scripts to execute" section in my transformation and execute it , it is returning nothing but transformation got completed without any errors. No result written in my output file. The same behavior with the step "Execute Row SQL Script" by reading the input from sql file/data grid with query as input.

Transformation flow:

EXECUTE SQL STATEMENTS >> Microsoft Excel Output
EXECUTE SQL STATEMENTS >> Textfile Output

2. If I use "Table Input" step and write the query under "SQL" section , it is getting executed and giving the result.

Table Input >> Microsoft Excel Output
Table Input >> Textfile Output

Can anyone help me in understanding this behavior and context/use cases of these steps.

Answer 1

Thank you techies for your knowledge share on this . As per my understanding ,"Execute SQL statement" step is used to execute SQL statements like DDL, DML but it won't give any result to output stream except number of records impacted/affected(statistics) when we execute DML statements.

To track this statistics, there were optional fields give like insert stats, update stats, delete stats and read stats and based on your DML statement we can give the field name and number of records affected will be written as a value to that field. This can be noticed in "Preview data" under Transformation Execution results.

Answer 2

Let me explain one basic concept of pentaho pdi (kettle): all the actions on kettle happens with a row. If there is no row, there will be no action. So if you add a generate row step at the begining of your transformation, with one dummy row with some value you will see how your sql statement will be triggered.

At a glance pentaho works with this 2 premises:

1 Everything is an asyncronous flow

2 Every action happens at row level. (no row, no action) an input table step generates rows but an execute sql statement is not a input step type, is a transform step and expects rows generated already before this step. I think this two basics concepts can help to understand how ketle works.

Answer 3

The Execute SQL Statement does not provide any result. Its purpose is DDL (Data Definition Language) to drop/create/truncate/alter tables, and DML (Data Manipulation Language) insert/update/delete rows.

Two checks (among other) should become a second nature after the coding of every step:

1. Check the output columns (right click on the step, choose Output fields).
2. Make a preview of the results (right click on the step, choose Preview).