SQL Transformation - Query Mode Dynamic (Full Connection Information)

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.

Full Database Connection Information:

We can configure the SQL transformation to connect to a database with a Full Database connection Information. A Full Database connection Information is that you pass to the transformation at run time. When you configure the transformation to use a Full Database Connection, the Designer creates the ConnectString, DBUser, DBPasswd, CodePage and AdvancedOptions input port.

We can pass connection information for each input row. Configure the mapping to pass the connection object name to the ConnectString, DBUser, DBPasswd, CodePage and AdvancedOptions input port. To avoid datatype conversion errors, use a relational connection for the same database type that is configured in 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.

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. And added 4 new out ports for Full Database Connection Information. 

OutPut Ports: 

out_ConnectString: <database connection string>

out_DBUser: <database user id>

out_DBPasswd: <database password>

out_CodePage: <Informatica Code Page>

Note: Infa connection object name available at workflow manager to insert/delete/update data using SQL transformation. The connection information should created in worflow manager.

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

Database Connection: Dynamic Connection - Full Connection Information. 

By default it will create 6 ports ConnectString, DBUser, DBPasswd, CodePage, AdvancedOptions 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.