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

Script mode: The SQL transformation runs ANSI SQL scripts that are externally located. we pass a script name to the transformation with each input row. The SQL transformation outputs one row for each input row.

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

Below is the sample mapping:

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

Source: Script_Name port of 255 scale

Target: 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: Static Connection

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

check the below highlighted information as your selection or not?

 Next to to SQL Ports tab:

In SQL Query as the input port name as shown 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.

 Source File: The Source file contain the information of the sql script file name and the path.




And the Practices.sql may contain any sql statement like create, alter, drop, insert, update, delete or truncate as below: