SQL Transformation in Informatica

SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.

The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor. You can also pass the database connection information to the SQL transformation as an input data at run time.

The following SQL statements can be used in the SQL transformation.

• Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
• DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)
• DATA Retrieval Statement (SELECT)
• DATA Control Language Statements (GRANT, REVOKE)
• Transaction Control Statements (COMMIT, ROLLBACK)

Configuring SQL Transformation

The following options can be used to configure an SQL transformation

• Mode: SQL transformation runs either in script mode or query mode.
• Active/Passive: By default, SQL transformation is an active transformation. You can configure it as passive transformation.
• Database Type: The type of database that the SQL transformation connects to.
• Connection type: You can pass database connection information or you can use a connection object.

We will see how to create an SQL transformation in script mode, query mode and passing the dynamic database connection with examples.

Creating SQL Transformation in Query Mode

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.

You can create the following types of SQL queries

Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows.

Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query.

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.