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)
and the Department table
and the Destination table is Source Qualifier. As you can see, it is an Empty table
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 Qualifier Transformation Source Definition
Navigate to Analyzer and define your Sources. As we said before, we are using 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
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 Target table using Definition to understand the process of creating a target definition
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.
Create Source Qualifier Transformation in Informatica
Drag and drop the Employee, and Department from Sources folder to the mapping designer. Once you drag the source, Power Center designer will automatically create the source qualifier transformation (default transformation) for you.
Let me remove the Department Table Source Qualifier
Next, we are adding the Department columns to the Employee source qualifier. This is one way of adding two tables
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
That will open the below shown window as we shown below. Click OK to select the Department table and Employee table.
Now you can see the same image that you have seen before
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.
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: 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.
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.
Let me use the User Defined Join property to Join the Employee table and Department table
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.
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.
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.
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:
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.
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.
Let us open the SQL Server to check whether we successfully performed the Joining using the Source Qualifier Transformation in Informatica
