SQL Transformation - Query Mode Static

SQL Transformation - Query Mode Static:

The SQL transformation processes SQL queries midstream in a pipeline. You can insert, delete, update, and retrieve rows from a database. You can pass the database connection information to the SQL transformation as input data at run time. The transformation processes external SQL scripts or SQL queries that you create in an SQL editor. The SQL transformation processes the query and returns rows and database errors.

For example, you might need to create database tables before adding new transactions. You can create an SQL transformation to create the tables in a workflow. The SQL transformation returns database errors in an output port. You can configure another workflow to run if the SQL transformation returns no errors.

When you create an SQL transformation, you configure the following options:

Query Mode:

When an SQL transformation runs in query mode, it executes an SQL query that you define in the transformation. You pass strings or parameters to the query from the transformation input ports to change the query statement or the query data.

When we configure the SQL transformation to run in query mode, we create an active transformation. The transformation can return multiple rows for each input row.

Create queries in the SQL transformation SQL Editor:

To create a query, type the query statement in the SQL Editor main window. The SQL Editor provides a list of the transformation ports that you can reference in the query. You can double-click a port name to add it as a query parameter.

When we create a query, the SQL Editor validates the port names in the query. It also verifies that the ports you use for string substitution are string datatypes. The SQL Editor does not validate the syntax of the SQL query.

You can create the following types of SQL queries in the SQL transformation:

·         Static SQL query: The query statement does not change, but you can use query parameters to change the data. The Integration Service prepares the query once and runs the query for all input rows.

·         Dynamic SQL query: You can change the query statements and the data. The Integration Service prepares a query for each input row.

When we create a static query, the Integration Service prepares the SQL procedure once and executes it for each row. When you create a dynamic query, the Integration Service prepares the SQL for each input row. We can optimize performance by creating static queries.

Sample Mapping:

1. Create or Import the source and target definition into Mapping Designer workspace as show below.

2. Add SQL Transformation to the mapping.
Mode: Query Mode
DB Type: Oracle (target database)
Database Connection: Static Connection

3. Select 'SQL Transformation' edit it and go to tab: SQL Settings

check the below highlighted information as your selection or not?

4. Drag the required ports/columns to the SQL transformation as show below

Note:
i) By default Native Type is blank, so we need to select appropriate datatypes which is required for SQL Transformation to perform DDL option here as show in below snap shot.
ii) Un-check ports which you don't want as output ports (as here we are trying to insert data using SQL Transformation and not passing any values to other transformation)
iii) It will prepare sql statement for each row passes through it.


SQL Query: Prepare the insert statement as above using table name in which you need to insert/delete/update the data by click the down arrow at the right side.

5. To avoid creating blank in target file (optional). Add the below logic.

6. Drag or mapping required columns/ports to the target instance.

7. To the passes the relation connection go the Workflow --> Session --> Mapping tab.

Pass the relation connection details to source (in case relation db) and Sql Transformation as show below.