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 Definition for Informatica Source Qualifier Transformation
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
Create Target Definition
Please navigate to Target Designer to define the Target. In this Informatica Source Qualifier Transformation 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
Create Mapping
To create a new mapping for Informatica Source Qualifier Transformation, 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.
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.
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 the Informatica Source Qualifier, 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.
That will open the below-shown window as 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 Informatica Source Qualifier transformation to see and alter the properties. The 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.
The 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 Informatica 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 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 to the SQL Joins article to understand the condition or Joiner Transformation to understand the Joining concept.
Next, Drag and drop the target definition (SourceQualifier) from the Targets folder to the mapping designer. Next, connect the Informatica 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.
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.
Once we have created the workflow, our next step is to create a session task for our mapping.
Create Informatica Source Qualifier Transformation 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.
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.
Let us open the SQL Server to check whether we successfully performed the Joining using the Source Qualifier Transformation.
