SQL Transformation - Query Mode Dynamic (Connection Object)

SQL Transformation - Query Mode Dynamic Connection Object:

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.

Logical Database Connection:

We can configure the SQL transformation to connect to a database with a logical database connection. A logical database connection is a connection object name that you pass to the transformation at run time. Define the relational connection object in the Workflow Manager. When you configure the transformation to use a logical database connection, the Designer creates the LogicalConnectionObject input port.

We can pass a logical connection for each input row. Configure the mapping to pass the connection object name to the LogicalConnectionObject port. To avoid datatype conversion errors, use a relational connection for the same database type that is configured in the transformation.

Sample Mapping:

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

2. Add a expression transformation next to source qualifier and drag all the required ports from SQ.

Add a output port LogicalConnectionObject=<pass the infa connection object name>
Note: Infa connection object name available at workflow manager to insert/delete/update data using SQL transformation.

3. Add SQL Transformation to the mapping.
Mode: Query Mode
DB Type: Oracle (target database)

Database Connection: Dynamic Connection - Connection Object.  

By default it will create 2 port LogicalConnectionObject input port and SQLError output port as below.

Drag or map the ports required from Expression to SQL Transformation.

Go the SQL settings tab to check the below highlighted information as your selection or not?

Go to SQL Ports tab:

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.

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

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

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