SQL Transformation - Script 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:

Script Mode:

An SQL transformation running in script mode runs SQL scripts from text files. We pass each script file name from the source to the SQL transformation ScriptName port. The script file name contains the complete path to the script file.

When we configure the transformation to run in script mode, we create a passive transformation. The transformation returns one row for each input row. The output row contains results of the query and any database error.

When the SQL transformation runs in script mode, the query statement and query data do not change. When you need to run different queries in script mode, you pass the scripts in the source data. Use script mode to run data definition queries such as creating or dropping tables.

When we configure an SQL transformation to run in script mode, the Designer adds the ScriptName input port to the transformation. When you create a mapping, you connect the ScriptName port to a port that contains the name of a script to execute for each row. You can execute a different SQL script for each input row. The Designer creates default ports that return information about query results.

An SQL transformation configured for script mode has the following default ports:

Port	Type	Description
ScriptName	Input	Receives the name of the script to execute for the current row.
ScriptResult	Output	Returns PASSED if the script execution succeeds for the row. Otherwise contains FAILED.
ScriptError	Output	Returns errors that occur when a script fails for a row.

Note: The input file contains the information of the sql file name and path which contain insert, update or delete statements.

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.

Below is the sample mapping:

1. Create or import an source & target instance as below snapshot.

Source: Script_Name port of 255 scaleTarget: ScriptResult port of 64 scale, ScriptError port of 4096 scale

2. Add SQL Transformation to the mapping.
Mode: Script Mode
DB Type: Oracle (target database)
Database Connection: Dynamic Connection - Connection Object.  

3. By Default it will create 2 input and 2 output ports as below snap shot.

Input Ports:

ScriptName: Input Source File Name

LogicalConnectionObject: The relation connection created in workflow manager to load data into target instance.

Next, edit SQL Transformation and go the SQL Setting tab:

check the below highlighted information as your selection or not?

Next, go to SQL Ports tab: 

SQL Query: It will required Script Name and Connection Object name as below

4.Then link the ports below SQ to SQL Transformation and SQL Transformation to Target instance.

Validate the mapping and save it.

5. Go the Workflow Manager and Create a workflow for the same.
6. Create a session for the above mapping.
7. Edit the session and go to Mapping tab, to pass source file details.

Note: For Sql Transformation connection details passing.
1. Choose connection Type as None (In case you want to defined schema/dataname in the ddl's)

2 Choose connection Type as Relation (In case you not defined any schema/database name in ddl's)

Source File: The Source file contain the information of the sql script file name & the file path and Infa relation Connection name.