SQL Transformation in Informatica

SQL transformation in Informatica process the Scrips and SQL queries midstream in the pipeline. You can insert, delete, update and retrieves rows into or from the database. You can pass the DB connection information to the SQL transformation at run time also as input.

SQL transformation in Informatica runs in one of the following modes.

• Script mode: A SQL t/r running in a script mode runs the SQL scripts from the text files.
• Query mode: A SQL t/r runs the queries that you put in the SQL transformation in the mapping.

 In this Informatica tutorial, we will generate a delimited file with pipe delimiter after finding the count of each source table using the SQL transformation. Check the following details given below.

Source Tables	Transformations	Logic	Target Tables
Source File as below	SQ_ Employee	N/A	Target File as below
Source File as below	SQL_COUNT(SQL T/R)	SELECT COUNT(*) FROM ‘$Table_Name’;	Target File as below

How to Create SQL Transformation in Informatica?

1. Source File Name: Process_Tables.txt.

Source table will be created as shown below

Table_Name

EMPLOYEE

CUSTOMER

PRODUCT

GLB_REGIONS

US_REGIONS

Create a file with above data as below

Target File name: Process_Tables_CNT.txt

Target table is created with the rows as given below

Table_Name      Table_CNT (how many rows in table level)

EMPLOYEE                        11

CUSTOMER        10

PRODUCT                         14

GLB_REGIONS   3

US_REGIONS     3

Note:- Target Table structure we will create from mapping.

Creating SQL Transformation in Informatica step by step

1. Import source file into source analyzer.

As it has only one column so this requires no delimiter to set.

2. Create a mapping with name ‘m_TableRecord_Count‘.
3. Drag source in to the mapping workspace.

4. Now right click on source qualifier and click on ‘create and add target’ option.

5. It creates a target table with same source qualifier structure based on Oracle database as default.

6. Now this will be storing on left side target components level. Now go to target designer and drag target table to designer.

7. Double click on table and go to Table tab and go to Database type.

8. Now go to Delimited Advanced and provide Pipe(|) as delimiter.

9. Now go to columns tab and create one more column with name ‘Table_count’ with number data type.

10. Now go to mapping designer and create SQL transformation in Query mode with name sql_count.

11. Now drag port from Source Qualifier to SQL Transformation as shown below.

12. Double click on Transformation and go to ‘SQL ports’ and create one OUTPUT port with name TableRecord_CNT

13. Now go to SQL Query in the below window and enter the following query shown below.

SELECT COUNT(*) FROM ~Table_Name~;.

14. Drag the both the ports from SQL to target definition.

15. Now save mapping and go to workflow manager, create session.
16. Now define source file properties and target file properties as required.

17. Also need to define database connection for SQL transformation

18. Now create workflow and execute it with session.
19. Now observe target file at target file location.

Target file gets data as below. May be count of records may change but structure is same.