Source Qualifier Transformation in Informatica

The Source Qualifier Transformation in Informatica is an Active, and Connected transformation. While creating a mapping, this is the default transformation generated by the power Center Designer.

This Informatica Source Qualifier Transformation converts the source data types to the native (Informatica) data types. In real-time, you can use this Informatica Source Qualifier Transformation to Join Multiple Sources, Write a custom query, Filter Rows, or Select Unique Records

For this Informatica Source Qualifier Transformation example, we are going to use the below show data (Employee table)

Source Qualifier Transformation in Informatica 1

and the Department table

Source Qualifier Transformation in Informatica 2

and the Destination table is Source Qualifier. As you can see, it is an Empty table

Source Qualifier Transformation in Informatica 3

Configure Source Qualifier Transformation in Informatica

Before we start configuring the Informatica source qualifier transformation, First connect to Informatica repository service by providing the Informatica Admin Console credential.

TIP: Here you have to provide the Informatica Admin Console Username and password that you specified while installing the Informatica Server.

Create Source Qualifier Transformation Source Definition

Navigate to Source Analyzer and define your Sources. As we said before, we are using Employee and Department tables from SQL Server database as our Informatica source definitions. Please refer Database Source in Informatica to understand the creation of source definition

Source Qualifier Transformation in Informatica 4

Create Informatica Source Qualifier Target Definition

Please navigate to Target Designer to define the Target. In this example, we are using the existing SQL table (SourceQualifier) as our target definition. You can refer to Create Informatica Target table using Source Definition to understand the process of creating a target definition

Source Qualifier Transformation in Informatica 5

Create Source Qualifier Transformation in Informatica Mapping

To create a new mapping, Please navigate to Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below. Here, you have to write a unique name for this mapping (m_Source_Qualifier) and click OK button.

Source Qualifier Transformation in Informatica 6

Create Source Qualifier Transformation in Informatica

Drag and drop the Employee, and Department source definitions from Sources folder to the mapping designer. Once you drag the source (any source), Power Center designer will automatically create the source qualifier transformation (default transformation) for you.

Source Qualifier Transformation in Informatica 7

Let me remove the Department Table Source Qualifier

Source Qualifier Transformation in Informatica 8

Next, we are adding the Department source columns to the Employee source qualifier. This is one way of adding two tables

Source Qualifier Transformation in Informatica 9

Let me remove the SQ_Employee (Employee Source Qualifier as well).

Create Informatica Source Qualifier Transformation

To explicitly create Source Qualifier transformation in Informatica, Please navigate to Transformation menu in Menu Bar. Next, select the Create.. option will open the Create Transformation window as shown below.

Please select the Source Qualifier Transformation from the drop-down list and provide the unique name (SQ_EmployeeDetails) for this transformation and click on Create button

Source Qualifier Transformation in Informatica 10

That will open the Select Sources for Source Qualifier Transformation window as we shown below. Click OK to select the Department table and Employee table as the sources.

Source Qualifier Transformation in Informatica 11

Now you can see the same image that you have seen before

Source Qualifier Transformation in Informatica 12

Double click on the Source Qualifier transformation to see, and alter the properties. Below screenshot shows you the list of available properties in the Transformation tab:

  • Select Transformation: By default, it will select the transformation you selected (or clicked on).
  • Rename: This button helps you to rename the Source Qualifier transformation name.
  • Description: Use this place to provide a valid description of this transformation.
Source Qualifier Transformation in Informatica 13

Below screenshot shows you the available options in the Ports tab:

  • Port Name: List of available column names. Use New column button to add new columns, scissors button to delete the unwanted columns. Here, we are deleting the DeptID column because it is a duplicate column
  • I: Source Qualifier Input columns.
  • O: Here, Columns that are check-marked are the Source Qualifier transformation Output columns. If you unchecked any column then, that column will not be available to load in a target table.
Source Qualifier Transformation in Informatica 14

Under Properties we have:

  • SQL Query: Use this property to write a Custom SQL Query. This query will replace the default SQL Query generated by the Source.
  • User Defined Join: Use this property to Join multiple Sources.
  • Source Filter: This property is used to filter the selected rows. It is Equal to Where Clause in SQL, or any database.
  • Number of Sorted Ports: This is used to sort the data. Similar to Order By Clause
  • Tracing Level: Specify how you want to trace (Detailed)
  • Select Distinct: This property is used to select the Distinct (Unique records) from the Source. Similar to SQL DISTINCT
  • Pre SQL: Use this property to run the SQL Command against the Source before the Integration Service Starts.
  • Post SQL: Use this property to run the SQL Command against the Source After the Integration Service Ends.
Source Qualifier Transformation in Informatica 15

Let me use the User Defined Join property to Join the Employee table and Department table

Source Qualifier Transformation in Informatica 16

As you can see we are using the below condition. You can refer SQL Joins article to understand the condition, or Joiner Transformation to understand the Joining concept.

Source Qualifier Transformation in Informatica 17

Next, Drag and drop the target definition (SourceQualifier) from Targets folder to the mapping designer. Next, connect the source qualifier Transformation with the target definition. Please use the Autolink.. option to connect them.

Before we close the Mapping, Let us Save, and Validate the mapping by going to Mapping Menu bar, and select the Validate option.

Source Qualifier Transformation in Informatica 18

Create Source Qualifier Transformation in Informatica Workflow

After you finish creating the Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create a workflow.

In this example, we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option. This will open Create Workflow window as shown below. Please provide the unique name (wf_Source_Qualifier) and leave the default settings.

Source Qualifier Transformation in Informatica 19

Once we created the workflow, our next step is to create session task for our mapping.

Create Source Qualifier Session

There are two types of sessions in informatica:

For this example we created a Reusable Session, and named it as s_EmployeeDept_fromSQL_ToSQL for the session.Please link the Start Task, and the Session Task. Next, navigate to Workflows Menu and select the Validate option to validate the Workflow.

Source Qualifier Transformation in Informatica 22

From the above screenshot you can observe that the Source Qualifier Transformation in Informatica workflow is a valid one.

Now, Let me start the Workflow. To do so, navigate to Workflows menu, and select the Start Workflow option.

Source Qualifier Transformation in Informatica 23

Let us open the SQL Server to check whether we successfully performed the Joining using the Source Qualifier Transformation in Informatica

Source Qualifier Transformation in Informatica 24