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.

Difference between Router and Filter transformation in Informatica

Router	Filter
Router transformation provides us the facility to capture the rows of data that do not meet any of the conditions to a default output group.	A Filter transformation tests data for one condition and drops the rows of data that do not meet the condition.
Router transformation is a single input and multi output group transformation.	Filter is single input and single output group transformation.
In a router transformation, you can specify more than one filter condition.	Filter transformation, you can specify only one filter condition.
The router transformation does not block input rows and those records that failed the filter condition will be passed to the default group	In a filter transformation there is chance that records get blocked
Router transformation acts like IIF condition in informatica or CASE.. WHEN in database.	Filter transformation works as WHERE clause of SQL .

Advantages of Router Transformation over Filter Transformation

• Better Performance; because in mapping, the Router transformation Informatica server processes the input data only once instead of as many times as you have conditions in Filter transformation.

• Less complexity; because we use only one Router transformation instead of multiple Filter transformation.

• Router transformation  is more efficient than the Filter transformation.

For Eg:

We have 3 departments in source now we want to send these records into 3 tables.To achieve this we require only one Router transformation.In case we want to get same result with Filter transformation then we require at least 3 Filter transformations.

Similarity:

A Router and Filter transformation are almost same because both transformations allow you to use a condition to test data.