Source Qualifier Transformation in Informatica

Source Qualifier Transformation in Informatica

The source qualifier transformation is an active,connected transformation used to represent the rows that the integrations service reads when it runs a session. You need to connect the source qualifier transformation to the relational or flat file definition in a mapping. The source qualifier transformation converts the source data types to the Informatica native data types. So, you should not alter the data types of the ports in the source qualifiertransformation.

The source qualifier transformation is used to do the following tasks:

• Joins: You can join two or more tables from the same source database. By default the sources are joined based on the primary key-foreign key relationships. This can be changed by explicitly specifying the join condition in the "user-defined join" property.
• Filter rows: You can filter the rows from the source database. The integration service adds a WHERE clause to the default query.
• Sorting input: You can sort the source data by specifying the number for sorted ports. The Integration Service adds an ORDER BY clause to the default SQL query
• Distinct rows: You can get distinct rows from the source by choosing the "Select Distinct" property. The Integration Service adds a SELECT DISTINCT statement to the default SQL query.
• Custom SQL Query: You can write your own SQL query to do calculations.

Creating Source Qualifier Transformation:

The easiest method to create a source qualifier transformation is to drag the source definition in to a mapping. This will create the source qualifier transformation automatically.

Follow the below steps to create the source qualifier transformation manually.

• Click Transformation -> Create.
• Select the Source Qualifier transformation.
• Enter a name for the transformation
• Click on create.

• Select a source, click OK and then click Done.

Now you can see in the below image how the source qualifier transformation is connected to the source definition.

Source Qualifier Transformation Properties:

We can configure the following source qualifier transformation properties on the properties tab. To go to the properties tab, open the source qualifier transformation by double clicking on it and then click on the properties tab.

Property	Description
SQL Query	To specify a custom query which replaces the default query.
User-Defined Join	Condition used for joining multiple sources.
Source Filter	Specifies the filter condition the Integration Service applies when querying rows.
Number of Sorted Ports	Used for sorting the source data
Tracing Level	Sets the amount of detail included in the session log when you run a session containing this transformation.
Select Distinct	To select only unique rows from the source.
Pre-SQL	Pre-session SQL commands to run against the source database before the Integration Service reads the source.
Post-SQL	Post-session SQL commands to run against the source database after the Integration Service writes to the target.
Output is Deterministic	Specify only when the source output does not change between session runs.
Output is Repeatable	Specify only when the order of the source output is same between the session runs.

Note: For flat file source definitions, all the properties except the Tracing level will be disabled.