You can pass input port values to parameters in the query or stored procedure. The transformation can insert, delete, update, and retrieve rows from a database. You can run SQL DDL statements to create a table or drop a table midstream in a mapping. The SQL transformation is an active transformation. The transformation can return multiple rows for each input row.

You can import a stored procedure from a database to the SQL transformation. When you import the stored procedure, the Developer tool creates the transformation ports that correspond to the parameters in the stored procedure. The Developer tool also creates the stored procedure call for you.

To configure an SQL transformation to run a stored procedure, perform the following tasks:

Define the transformation properties including the database type to connect to.
Import a stored procedure to define the ports and create the stored procedure call.
Manually define ports for result sets or additional stored procedures that you need to run.
Add the additional stored procedure calls in the SQL Editor.

You can configure an SQL query in the transformation SQL Editor. When you run the SQL transformation, the transformation processes the query, returns rows, and returns any database error.

To configure an SQL transformation to run a query, perform the following tasks:

Define the transformation properties including the database type to connect to.
Define the input and output ports.
Create an SQL query in the SQL Editor.

After you configure the transformation, configure the SQL transformation in a mapping and connect the upstream ports. Preview the data to verify the results.