Source Qualifier Transformation in Informatica with EXAMPLE

What is Source Qualifier Transformation?

Source qualifier transformation is an active, connected transformation which is used to represent the rows that the integrations service read. Whenever we add a relational source or a flat file to a mapping, a source qualifier transformation is required. When we add a source to a mapping, source qualifier transformation is added automatically. With source qualifier, we can define and override how the data is fetched from the source.

In the following example we would be modifying source qualifier of our mapping “m_emp_emp_target”, so instead of returning all the columns it will return only selected columns.

Step 1) Open mapping “m_emp_emp_target” in mapping designer.

Step 2) Double click on the source Qualifier transformation “SQ_EMP”. It will open edit transformation property window for it. Then

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

Step 3) In the SQL editor window

1. Enter the following query
   SELECT EMPNO, ENAME, JOB, MGR FROM EMP

   Note – we are selecting the columns EMPNO, ENAME, JOB & MANAGER from the source, so we have kept only those in the select query

2. Select OK Button

Step 4) In the “edit transformations” window,

1. Select Ports tab from the menu
2. Under ports tab, you will see all the ports. Keep only the ports EMPNO, ENAME, JOB, MGR and delete other ports

Step 5) After deletion of ports, Select OK Button

Now, again click on properties tab in Edit Transformations window, and you will see only those data that you have selected.

When you click on “OK” button it will open SQL Editor Window.

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

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

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

Properties of Source Qualifier

You can use various properties of Source Qualifier, to determine what type of source data needs to transform to target table.

1. Source Filter – Using the source filter property you can filter the number of source records. For example, you want to fetch only the employees of deptno 10, then you can enter the filter condition deptno=10 in source filter property and execute the data.
2. Number for sorted ports – In source qualifier transformation, you can also sort the input records based on the ports number. So when the data is passed on to the transformations inside the mapping, it will read the port number and sort the data accordingly.

   As data can be sorted based on a single or multiple ports, you have to give the number of ports which will be used in sorting. If you give value as 1, then only empno data will be sorted. If you give value as 2 then on empno and ename on both columns data will be sorted.

3. Select Distinct – you can fetch only distinct records from the source using this property. When you select the select distinct option, only distinct combination of source data will be fetched by source qualifier.