Informatica SQL Transformation, SQLs Beyond Pre & Post Session Commands

  Transformations

SQL statements can be used as part of pre or post SQL commands in a PowerCenter workflow. These are static SQLs and can run only once before or after the mapping pipeline is run. With the help of SQL transformation, we can use SQL statements much more effectively to build your ETL logic. In this tutorial lets learn more about the transformation and its usage with a real time use case.

What is SQL Transformation

The SQL transformation can be used to processes SQL queries midstream in a mapping. You can execute any valid SQL statement using this transformation. This can be external SQL scripts or SQL queries that are created with in the transformation. SQL transformation processes the query and returns rows and database errors if any.

Configuring SQL Transformation

SQL transformation can run in two different modes.

• Script mode :- Runs SQL scripts from text files that are externally located. You pass a script name to the transformation with each input row. It outputs script execution status and any script error. 
• Query mode :- Executes a query that you define in a query editor. You can pass strings or parameters to the query to define dynamic queries. You can output multiple rows when the query has a SELECT statement.

Script Mode

An SQL transformation running in script mode runs SQL scripts from text files. It creates an SQL procedure and sends it to the database to process. The database validates the SQL and executes the query. You cannot use scripting languages such as Oracle PL/SQL or Microsoft/Sybase T-SQL in the script.

In the script mode, you pass script file name with the complete path from the source to the SQL transformation ScriptName port. ScriptResult port gives the status of the script execution status. It will be either PASSED or FAILED. ScriptError returns errors that occur when a script fails for a row. 

Above shown is an SQL transformation in Script Mode, which will have a ScriptName input and ScripResult, ScriptError as output.

Query Mode

When SQL transformation runs in query mode, it executes an SQL query defined in the transformation. You can pass strings or parameters to the query from the transformation input ports to change the SQL query statement or the query data. The SQL query can be static or dynamic.

• Static SQL query :- The query statement does not change, but you can use query parameters to change the data, which is passed in through the input ports of the transformation. 
• Dynamic SQL query :- You can change the query statements and the data, which is passed in through the input ports of the transformation.

With static query, the Integration Service prepares the SQL statement once and executes it for each row. With a dynamic query, the Integration Service prepares the SQL for each input row.

Above shown SQL transformation, which runs in query mode has two input parameters and returns one output.

SQL Transformation Use Case

Lets consider the ETL for loading Dimension tables into a data warehouse. The surrogate key for each of the dimension tables are populated using an Oracle Sequence. The ETL architect needs to create an Informatica reusable component, which can be reused in different dimension table loads to populate the surrogate key.

Solution : Here lets create a reusable SQL transformation in Query mode, which can take the name of the oracle sequence generator, and pass the sequence number as the output.

Step 1 :- Once you have  the  transformation developer open you can start creating the SQL transformation like any other transformations. It opens up a window like shown in below image.

This screen will let you choose the mode, database type, database connection type and you can make the transformation active or passive. If the database connection type is dynamic, you can dynamically pass in the connection details into the transformation. If the SQL query returns more than one record, you need to make the transformation active.

Step 2 :- Now create the input and output ports as shown in the below image. We are passing in the database schema name and the sequence name. It return sequence number as an output port.

Step 3 :- Using the SQL query editor, we can build the query to get the sequence generator. Using the 'String Substitution' ports we can make the SQL dynamic. Here we are making the query dynamic by passing the schema name, sequence name dynamically as an input port.

That is all we need for the reusable SQL transformation. Below shown is the completed SQL transformation, which can take two input values (schema name, sequence name) and returns one output value (sequence number).

Step 4 :- We can use this transformation just like any other reusable transformations, Need to pass in the schema name, sequence name as input ports and returns sequence number, which can be used to populate the surrogate key of the dimension table as shown below.

As per the above example, integration service will convert the SQL as follows during the session runtime. SELECT DW.S_CUST_DIM.NEXTVAL FROM DUAL;

Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out this tutorial or share us if you use any different use cases you want to implement using SQL transformation.

Share with your friends