SQL Transformation - Script Mode Dynamic (Full Connection Information)

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.

We can pass all the database connection information to an SQL transformation as input port data. When you configure the SQL transformation to connect to a database with a full connection, the Designer creates input ports for connection components. The database type defaults to the database type you configured for the transformation.

The following table describes the ports that the Designer creates when you configure an SQL transformation to connect to a database with a full connection:

Port	Required/ Optional	Description
ConnectString	Required	Contains the database name and database server name.
DBUser	Required	Name of the user with permissions to read and write from the database.
DBPasswd	Required	DBUser password.
CodePage	Optional	Code page the Integration Service uses to read from or write to the database. Use the ISO code page name, such as ISO-8859-6. The code page name is not case sensitive.
AdvancedOptions	Optional	Connection attributes. Pass the attributes as name-value pairs. Delimit each attribute from another with a semicolon. Attribute names are not case sensitive.

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 ModeDB Type: Oracle (target database)Database Connection: Dynamic Connection - Full Connection Information

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

check the below highlighted information as your selection or not?

By Default it will create input ports ScriptName, ConnectString, DBUser, DBPasswd, CodePage & AdvanceOptions (Optional) and 2 output port ScritpResult & ScriptError.

3. Select 'SQL Transformation' edit it and go to tab: SQL Settings check the below highlighted information as your selection or not?

Next, go to SQL Ports tab:

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

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.

Validate the mapping and save it.

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 & file path, Connection String, DB Username, DB Password, Code Page and System Commands.