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 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 custom query, Filter Rows, or Select Unique Records
In this article we are going to explain the steps involved in configuring the Source Qualifier Transformation in Informatica with example. For this 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
TIP : Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Step 1: Create Source Definition
Once you connected successfully, Please 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 source definitions. Please refer Database Source in Informatica to understand the creation of source definition
Step 2: Create 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 Create Informatica Target table using Source Definition to understand the process of creating target definition
Step 3: Create 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 unique name for this mapping (m_Source_Qualifier) and click OK button.
TIP : Please refer Informatica Mapping article to understand the procedure for creating Mapping
Step 3(a): 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.
Let me remove the Department Table Source Qualifier
Next, we are adding the Department source 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).
Step 3(b): Create Informatica Source Qualifier Transformation
In order 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 drop down list and provide the unique name (SQ_EmployeeDetails) for this transformation and click on Create button
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.
Now you can see the same image that you seen before
Double click on the Source Qualifier transformation to see, and alter the properties. Below screenshot show you the list of available properties in 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 about this transformation.
Below screenshot show 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 target table.
Under Properties we have:
- SQL Query: Use this property to write 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.
Let me use the User Defined Join property to Join the Employee table, and Department table
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.
Step 4: Create Workflow
After you finish creating the Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create 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.
Step 4(a): Create Session
There are two types of sessions in informatica:
- Non-reusable Session Task: Please refer Session in Informatica article to understand the steps.
- Reusable Session Task: Please refer Reusable Session in Informatica article to understand the steps involved in it.
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 Management Studio to check whether we successfully performed the Joining using the Source Qualifier Transformation in Informatica
Thank You for visiting Our Blog