Source Qualifer Transformation

1. What is a source qualifier transformation?

A source qualifier represents the rows that the integration service reads when it runs a session. Source qualifier is an active transformation. Source qualifier is an active transformation.

2. Why you need a source qualifier transformation?

The source qualifier transformation converts the source data types into informatica native data types.

3. What is the default join in source qualifier transformation?

The source qualifier transformation joins the tables based on the primary key-foreign key relationship.

4. How to create a custom join in source qualifier transformation?

When there is no primary key-foreign key relationship between the tables, you can specify a custom join using the 'user-defined join' option in the properties tab of source qualifier.

5. How to join heterogeneous sources and flat files?

Use joiner transformation to join heterogeneous sources and flat files

6. How do you configure a source qualifier transformation?

·                     SQL Query

·                     User-Defined Join

·                     Source Filter

·                     Number of Sorted Ports

·                     Select Distinct

·                     Pre-SQL

·                     Post-SQL

7. What happens to a mapping if we alter the data types between Source and its corresponding Source Qualifier? 
Ans. The Source Qualifier transformation displays the transformation datatypes. The transformation datatypes determine how the source database binds data when the Integration Service reads it.

Now if we alter the datatypes in the Source Qualifier transformation or the datatypes in the source definition and Source Qualifier transformation do not match, the Designer marks the mapping as invalid when we save it.

8. Suppose we have used the Select Distinct and the Number Of Sorted Ports property in the SQ and then we add Custom SQL Query. Explain what will happen. 
Ans. Whenever we add Custom SQL or SQL override query it overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation. Hence only the user defined SQL Query will be fired in the database and all the other options will be ignored.

9. Describe the situations where we will use the Source Filter, Select Distinct and Number Of Sorted Ports properties of Source Qualifier transformation. 
Ans. Source Filter option is used basically to reduce the number of rows the Integration Service queries so as to improve performance.

Select Distinct option is used when we want the Integration Service to select unique values from a source, filtering out unnecessary data earlier in the data flow, which might improve performance.

Number Of Sorted Ports option is used when we want the source data to be in a sorted fashion so as to use the same in some following transformations like Aggregator or Joiner, those when configured for sorted input will improve the performance.

10. What will happen if the SELECT list COLUMNS in the Custom override SQL Query and the OUTPUT PORTS order in SQ transformation do not match? 
Ans. Mismatch or Changing the order of the list of selected columns to that of the connected transformation output ports may result is session failure.

11. What happens if in the Source Filter property of SQ transformation we include keyword WHERE say, WHERE

CUSTOMERS.CUSTOMER_ID > 1000. 
Ans. We use source filter to reduce the number of source records.If we include the string WHERE in the source filter, the Integration Service fails the session.

 Download as PDF