SQL transformation in Informatica for executing SQL scripts and dynamic SQLs

SQL transformation in Informatica for executing SQL scripts and dynamic SQLs

If you have some SQLs in a file and need to execute it on a database using Informatica or if you want to use some transformation to create dynamic SQLs i,e SQLs that take parameter and give you the output based on the input parameter, then SQL transformation is a way to go.

The SQL transfomation operates in Script and Query mode. In query mode you can write a query in the SQL transformatica and specify a parameter in the query which is the input port and depending on the value of the input port the result could change. For example: if NAME is a input port in the SQL transformation, your query in SQL transformation could be " SELECT DEPARTMENT FROM DEPARTMENT_TABLE WHERE FIRST_NAME=?NAME?". This will return department name for the every NAME that goes through the SQL transformation.

If you want to execute bunch of SQLs that are in a file then create the SQL transformation in Script mode. Read the SQL from a flat file and pass the field corresponding to the SQL to the Script name field of the SQL transformation. The SQL transformation executes every SQL that it reads from the flat file and executes it. 

For example: If the input file has :

CREATE TABLE MYTABLE as SELECT * from MYSTAGING_TABLE;

InSERT into MYTABLE values ('xxx', 1 ,2 3).

The SQL transformation will execute both the statements one after another and return the script result and any ERROR outputs. Sample mapping is shown in the screenshot below: