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:
| Option | Description |
|---|---|
| SQL Query | It defines a custom query that replaces the default query of the Integration Service, which is used to read data from sources. A custom query overrides entries for a custom join or a source filter. |
| User-Defined Join | It specifies the condition which is used to join data from multiple sources represented in the same Source Qualifier transformation. |
| Source Filter | It specifies the filter condition of the Integration Service that applies while querying the rows. |
| Number of Sorted Ports | It indicates the number of columns, and it is used during the sorting rows queried from relational sources. If we go with this option, the Integration Service adds an ORDER BY to the default query when it reads source rows. The ORDER BY includes the number of ports specified, starting from the top of the transformation. When selected, the database sort order must match the session sort order. |
| Tracing Level | It sets the amount of detail included in the session log when we run a session containing this transformation. |
| Select Distinct | Specifies if you want to select unique rows. The Integration Service includes a SELECT DISTINCT statement if you choose this option. |
| Pre-SQL | Pre-session SQL commands are used to run against the source database before the Integration Service reads the source. |
| Post-SQL | Post-session SQL commands are used to run against the source database after the Integration Service writes to the target. |
| Output is Deterministic | Relational source or transformation output that does not change between session runs when the input data is consistent between runs. When you configure this property, the Integration Service does not stage source data for recovery if transformations in the pipeline always produce repeatable data. |
| Output is Repeatable | Relational source or transformation output that is in the same order between session runs when the request of the input data is consistent. When the output is deterministic, and output is repeatable, the Integration Service does not stage source data for recovery. |
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
- Click on the properties tab.
- Click on the SQLQuery Modify option, and this will open an SQL editor window.
Step 3: In the SQL editor window
- 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.
- Click on the OK button.
Step 4: In the “edit transformations” window,
- Select the Ports tab from the menu.
- 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
- It will confirm the data you have chosen are correct and ready for loading into the target table.
- 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.