Informatica Source Qualifier and Common Mistakes

Post date: 21-Jul-2010 05:43:18

Anybody working on Informatica will know about the concept of Source Qualifier. For those who are new to Informatica I will just try to explain in plain language.

Source qualifier can be termed as a default transformation which comes up when we select source for the mapping. It provide a default SQL Query which can be generated by clicking Generate SQL (properties tab–>SQLQuery attribute). You can have multiple sources in a mapping but you can only have one source qualifier for the mapping .You can enter any SQL statement supported by your Source database with proper joins with other Sources.

When you drag a source (Represents data read from relational or flat file sources) into the Mapping Designer workspace, you add an instance of the source definition to the mapping and one Source Qualifier automatically comes with that, but if we are using multiple sources then multiple Source Qualifier will automatically pop up (as shown in the screenshot below) which requires us to delete the additional Source Qualifier.

After deleting the additional source qualifiers, we need to modify the single source qualifier manually and supply information like additional Transformation Ports and SQL. Now, there are some common mistakes that a newbie can make while doing this that can result in waste of time. So, I am listing here some things that you should keep in mind while doing that. 

• The ports should be added in the same order as mentioned in the SQL Query.Wrong Ordering of Ports may give wrong result as the value of columns will be assigned to wrong ports. Lets understand this with an example.

• SELECT

• W_LOV_D.VAL,

• WC_SUB_PNL_D.PNL,

• WC_SUB_PNL_D.SUB_PNL,

• WC_SUB_PNL_D.BUSINESS,

• FROM

• WC_SUB_PNL_D,

• W_LOV_D

• Where W_LOV_D.ROW_WID= WC_SUB_PNL_D.SUBPNL_WID

  • Ports should be added under Ports Tab of source qualifier in this order as (do check the data type, precision and scale value also)

  • VAL

  • PNL

  • SUB_PNL

  • BUSINESS

• All columns mentioned in SQL Query should be included in Source Qualifier Transformation Port

• All the ports under Source Qualifier should be mapped with Expression else the Mapping would remain invalid

Keeping the above mentioned points in mind while making a new mapping can help you save precious time by avoiding common mistakes we make