Source Qualifier Transformation in Informatica with EXAMPLE

What is Source Qualifier Transformation?

Source Qualifier transformation is an active, connected transformation that represents the rows the Integration Service reads when a session runs. Whenever a relational source or a flat file is added to a mapping, a Source Qualifier transformation is required, and the Designer adds one automatically. With the Source Qualifier, you can define and override how the data is fetched from the source.

The transformation also converts the native data types of the source into Informatica transformation data types, so every transformation placed after it works with one platform-neutral set of types.

Because the Source Qualifier builds the query that actually reaches the database, it is the first place to look when a mapping reads more rows or more columns than it needs. The example below narrows the query down to four columns.

How to Modify Source Qualifier in Informatica

In the following example we will modify the Source Qualifier of our mapping “m_emp_emp_target”, so that instead of returning all the columns it returns only selected columns.

Step 1) Open the mapping “m_emp_emp_target” in Mapping Designer. The mapping opens with the EMP source, the SQ_EMP Source Qualifier and the EMP_TARGET target on the canvas.

Step 2) Double-click the Source Qualifier transformation “SQ_EMP”. It opens the Edit Transformations window for that object. Then

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

The Properties tab lists the SQL Query row shown below.

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 and MGR from the source, so we have kept only those in the select query

2. Select the OK button

The SQL Editor now holds the custom query instead of the generated one.

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 EMPNO, ENAME, JOB, MGR and delete the other ports

The Ports tab lists every port carried by the transformation before the deletion.

Step 5) After deletion of the ports, select the OK button. Only the four remaining ports are left in the list.

Now click the Properties tab again in the Edit Transformations window, and you will see only the data that you have selected.

When you click the “OK” button it will open the SQL Editor window.

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

The confirmation window repeats the four columns that will now be read.

Save the mapping (using the 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 the Source Qualifier which columns need to be fetched from the source, and this is the only way to control which specific columns are brought inside the mapping.

Properties of Source Qualifier

You can use various properties of the Source Qualifier to determine what type of source data needs to be transformed and sent to the target table.

1. Source Filter – Using the Source Filter property you can reduce the number of source records. For example, if you want to fetch only the employees of deptno 10, you can enter the filter condition deptno=10 in the Source Filter property and execute the data. Include the table name and the port name in the condition, and leave out the keyword WHERE – a filter containing WHERE fails the session.
2. Number of Sorted Ports – In the Source Qualifier transformation you can also sort the input records by port position. The Integration Service adds an ORDER BY clause to the default query, counting ports from the top of the transformation, so the data is already sorted when it reaches the transformations inside the mapping.

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

3. Select Distinct – you can fetch only distinct records from the source using this property. When you select the Select Distinct option, the Integration Service adds a SELECT DISTINCT statement, so only distinct combinations of source data are fetched by the Source Qualifier.

The Properties tab shown below carries these options together with the rest of the property list.

The full property list available on a relational Source Qualifier is summarised in the table below.

Property	What it does
SQL Query	Replaces the default query generated by the Integration Service. A custom query overrides a user-defined join and a source filter.
User-Defined Join	Supplies the condition used to join data from several sources represented by the same Source Qualifier.
Source Filter	Supplies the filter condition the Integration Service applies while querying rows.
Number of Sorted Ports	Number of columns used to sort rows read from relational sources, added to the query as ORDER BY.
Tracing Level	Amount of detail written to the session log for this transformation.
Select Distinct	Returns unique rows only by adding SELECT DISTINCT to the query.
Pre-SQL	Commands run against the source database before the Integration Service reads the source.
Post-SQL	Commands run against the source database after the Integration Service writes to the target.
Output is Deterministic	Declares that the source returns the same data between runs when the input is consistent.
Output is Repeatable	Declares that the source returns rows in the same order between runs, which lets the Integration Service skip staging for recovery.

Rules and Guidelines for a Source Qualifier SQL Override

An SQL override is powerful because it replaces everything the Designer generated, and that is also why it breaks quietly when the rules below are ignored.

• Connect the ports first. Link every input and output port you intend to use before the query is entered, because the generated query is built from the connected ports.
• Keep the SELECT list in port order. The SELECT statement must list the port names in the order in which they appear in the transformation. If the order does not match, the session can fail or return unexpected results.
• Qualify every column. Each column name must be prefixed by the table, view or synonym it belongs to, for example ORDERS.ORDER_ID.
• Generate before you edit. Clicking Generate SQL shows the default query, which already contains the source filter and the sorted-ports clause, so the override can be built from a working statement.
• Validate the statement. The SQL Editor has a Validate button that runs the query and reports whether the syntax is correct before the session is ever started.
• Quote reserved words. Any database reserved word used in the query has to be enclosed in quotes.
• Watch the precedence. A custom query overrides the source filter and the number of sorted ports, and an SQL query entered in the session properties overrides both the filter condition and the query defined in the mapping.

For Microsoft SQL Server sources there is one extra rule: the number of columns in the SELECT statement must match the number of ports in the transformation, or the session fails while fetching rows.

How to Join Two Sources with One Source Qualifier

A single Source Qualifier can read more than one table. When two related tables from the same relational source are linked to one Source Qualifier, the Integration Service joins them inside the source query rather than inside the mapping, which means the database does the work and fewer rows travel to the session.

The default join is an inner equijoin written into the WHERE clause as Source1.column_name = Source2.column_name. For that join to be generated, the joined columns must have a primary key-foreign key relationship and matching data types. Where the imported metadata carries no such relationship, you can create one in the Source Analyzer by linking the matching columns of the two source definitions.

When the relationship is missing or the join has to be an outer join, use the User-Defined Join property instead. The condition entered there replaces the join information held in the metadata, and the Integration Service appends it to the generated query.

Two limits are worth remembering. Both tables have to come from the same relational source, and sources of different types – a flat file and a table, or two different databases – must be joined with the Joiner transformation instead.

Source Qualifier vs Filter vs Joiner Transformation

Three objects can all reduce or combine rows, and choosing the wrong one is a common cause of slow sessions. The table below sets them side by side.

Aspect	Source Qualifier	Filter transformation	Joiner transformation
Where the work runs	In the source database, inside the generated query	In the Integration Service, row by row	In the Integration Service, using a cache
Main purpose	Read source rows, override the query, filter, sort, deduplicate	Drop rows that fail one condition	Combine two pipelines into one row set
Source types	Relational tables and flat files of one source	Any pipeline data	Heterogeneous sources, including flat files and different databases
Join support	Inner equijoin or a user-defined join within one relational source	None	Normal, master outer, detail outer and full outer joins
Rows crossing the network	Only the rows the query returns	Every source row is read first	Every source row is read first

The practical rule is simple: filter and join as early as the source allows, and reach for a Filter or a Joiner only for work the source database cannot do. Pushing conditions into the Source Qualifier is one of the cheapest wins available during performance tuning.

FAQs