Source Qualifier Transformation - Joins

Source Qualifier Transformation - Joins:

You can join two or more tables using the Source Qualifier Transformation. To achieve it, 

Set the Property of "User Defined Join" in the Properties Tab of the Edit Transformation.

Edit Transformations - Properties Tab

Mappng Designer

This works based on the below query:

select * from employees e,departments d where e.department_id=d.department_id;

Left Outer Join SQL Query - HR Schema

Source Qualifier Transformation - Filter

Source Qualifier Transformation - Filter:

Using Source Qualifier Transformation,

You can filter the rows of the source database by adding a where clause in the Source Filter in the Properties Tab of the Edit Transformation.

The Intergration Service adds a WHERE clause to the default query.

Edit Transformations - Properties Tab - Source Filter

Mappng Designer

This works based on the below query:

select * from employees e,departments d where e.department_id=d.department_id and d.department_name='IT';

Left Outer Join SQL Query - HR Schema

In the Workflow Manager, the session properties check the Source Filter Properties of Souce Qualifier Properties in the Mapping Tab.

Workflow Manager - Edit Task - Mapping Tab - SQ Properties - Source Filter

Source Qualifier Transformation - Sort

Source Qualifier Transformation - Sort:

Using Source Qualifier Transformation,

You can Sort the rows of the source by specifying the number of ports.

The Intergration Service adds a ORDER BY clause to the default SQL query.

Note: The Order of the column in the Source Qualifier Transformation should be order of number of ports which we specify for Sort Port.

Edit Source Qualifier Transformation - No of sorted ports

Mappng Designer

This works based on the below query:

select * from employees e,departments d where e.department_id=d.department_id and d.department_name='IT' ORDER BY first_name;

Left Outer Join SQL Query - HR Schema

In the Workflow Manager, the session properties check the Number of sorted ports Properties of Souce Qualifier Properties in the Mapping Tab.

Workflow Manager -Edit Task - Mapping Properties - SQ Properties