Source Qualifier Transformation

The source qualifier transformation is active and connected. It is used to represent the rows that the integrations service reads when it runs a session. We need to join the source qualifier transformation with the relational or flat file definition in a mapping.

The source qualifier transformation converts the source data types in the Informatica native data types. That's why there is no need to alter the data types of the ports.

The source qualifier transformation does the following tasks, such as:

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

Source Qualifier Transformation Properties

The source qualifier transformation has the following properties, such as:

Examples

In this example, we want to modify the source qualifier of the mapping "m_emp_emp_target", so instead of returning all the columns, it will return only selected columns.

Step 1: Open mapping "m_stud_stud_target" in mapping designer.

Step 2: Double click on the Source Qualifier transformation "SQ_STUD". It will open the edit transformation property window for it. Then

1. Click on the properties tab.
2. Click on the SQLQuery Modify option, and this will open an SQL editor window.

Step 3: In the SQL editor window

1. Enter the following query
   SELECT STUDNO, SNAME, CLASS, SEC FROM STUD

Note: we are selecting the columns STUDNO, SNAME, CLASS & SECTION from the source, so we have kept only those in the select query.

2. Click on the OK button.

Step 4: In the "edit transformations" window,

1. Select the Ports tab from the menu.
2. Under the ports tab, you will see all the ports. Keep only the ports STUDNO, SNAME, CLASS, SEC and delete other ports

Step 5: After the deletion of ports, click OK Button.

Now, again click on the properties tab in the Edit Transformations window, and we will see only selected data. After clicking the "OK" button it will open SQL Editor Window, and

1. It will confirm the data you have chosen are correct and ready for loading into the target table.
2. Click on the OK button.

Save the mapping (using ctrl+s) and execute the workflow. After execution, only the selected columns will be loaded into the target.

In this way, we can override in source qualifier what columns need to be fetched from the source & this is the only way to replace what specific columns will be brought inside the mapping.