SQL Transformation in Informatica

SQL Transformation will take the input in the form of  SQL scripts or SQL queries that you create in an SQL editor and processes the query and returns rows and database errors.It’s basically Active transformation which you can configure as passive also.For eg we may have a scenario where we need to create database tables in a workflow.Here we can make use of SQL Transformation and create these tables before we move into the next flow.

Configurations in SQL Transformation

• Mode:There are two modes 

Script Mode: In this mode we pass the name of the script as Input to the transformation 
Query Mode: In this mode we pass a Query having parameters or variables

• Database Types:The type of database that we are connecting

• Connection: The type of database connection that we use(FTP or some other)

 Now we will see each mode in Detail:

 Script mode:

As discussed in this mode we pass script name  as input to the transformation.And it returns the output rows are errors.

Input port: This will have the scripts name from previous level

Output Port: Returns PASSED if the script execution succeeds for the row. Otherwise contains FAILED.

Query Mode:

When an SQL transformation runs in query mode, it executes an SQL query that you define in the transformation.This creates an active transformation.It returns multiple rows.The Sql Editor validates the query.

You can create the following types of SQL queries in the SQL transformation:

1. 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.
2. Dynamic SQL query. You can change the query statements and the data. The Integration Service prepares a query for each input row.

SQL Transformation Properties

After you create the SQL transformation, you can define ports and set attributes in the following

transformation tabs:

• Ports.Displays the transformation ports and attributes that you create on the SQL Ports tab.
• Properties. SQL transformation general properties.
• SQL Settings. Attributes unique to the SQL transformation.
• SQL Ports. SQL transformation ports and attributes.