Skip to main content
Pentaho Documentation

Execute Row SQL Script

The Execute row SQL script step executes an SQL statement or file for every input row, allowing you to dynamically assemble SQL for the creation of indexes, partitions, and tables. 

Prepared SQL statements are not used due to the scripting and dynamic operation of this step, which can slow transformation performance. For optimal performance, Pentaho recommends using dedicated steps like Table Output, Table Input, Update, Delete, etc.

General

The following fields are general to this transformation step:

Field Description
Step name Specify the unique name of the Execute row SQL script transformation step on the canvas. You can customize the name or leave it as the default.
Connection Select the name of a connected database from the list.
Edit Click to edit your current database connection.
New Click to establish a new database connection.
Wizard Click to open a new database connection using the Wizard.
Commit Specify the number of rows to commit at a time. The default is 1.
Send a single statement Clear if you do not want to group rows into a single statement. 
SQL field name Specify the field that contains either the SQL statement or the path to the SQL file to execute.
Read SQL from file Select to specify that the SQL field name is the path to an SQL file.

Output Fields

PDI_ExecuteRowSQLScript_Dialog.png

The Execute row SQL script step features the following output field options for collecting statistics about the script processing: 

Option Description
Field to contain insert stats Specify a field name to create a field in the stream that contains the statistic for the number of records that were inserted.
Field to contain Update Specify a field name to create field in the stream that contains the statistic for the number of records that were updated.
Field to contain Delete stats Specify a field name to create a field in the stream that contains the statistic for the number of records that were deleted.
Field to contain Read stats Specify a field name to create a field in the stream that contains the statistic for the number of records that were read. 

Metadata Injection Support

All fields of this step support metadata injection. You can use this step with ETL Metadata Injection to pass metadata to your transformation at runtime.