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 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 Table 1

and the Department table

Department Table 2

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

Destination Table 3

Configure Source Qualifier Transformation in Informatica

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

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

Create Source Definition

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

Source Qualifier Transformation in Informatica 4

Create Target Definition

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

Add Table to target designer 5

Create Mapping

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

Add New Mapping Name

Create Source Qualifier Transformation in Informatica

Drag and drop the Employee and Department from the Sources folder to the mapping designer. Once you drag the source, the 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

click yes button 8

Next, we are adding the Department 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, Please navigate to the 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 below-shown window as shown below. Click OK to select the Department table and Employee table.

select the Source 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.
Edit Name 13

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

  • Port Name: List of available column names. Use the new column button to add new columns and the scissors button to delete the unwanted columns. Here, we are deleting the DeptID column because it is a duplicate column
  • I: Input columns.
  • O: Here, check-marked Columns 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.
Port Tab to add or remove columns 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 it.
  • User Defined Join: Use this property to Join multiple data sources.
  • Filter: This property is used to filter the selected rows. It is Equal to the 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). Similar to SQL DISTINCT
  • Pre SQL: Use this property to run the Command against the Source before the Integration Service Starts.
  • Post SQL: Use this property to run the Command against the Source After the Integration Service Ends.
properties window 15

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

user defined join option 16

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

Query Expression to Connect 17

Next, Drag and drop the target definition (SourceQualifier) from the 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 the Mapping Menu bar and selecting 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 creating 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.

create workflow 19

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

Create Source Qualifier Session

There are two types of sessions:

For this example, we created a Reusable Session and named it 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 the Workflows menu, and select the Start Workflow option.

Start The Workflow 23

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

Output Destination Table 24