How to remove duplicate records using Expression In the last post we have used Aggregator Transformation to remove the duplicate records. In this post we will use the Expression transformation to remove the duplicate JOB_ID from the source Flat file. Source – Flat File Target – Oracle Table Key port – JOB_ID Transformations Sorter – To… Continue reading How to remove duplicate records using Expression
Category: Informatica
How to remove duplicate records Using Aggregator
How to remove duplicate records Using Aggregator There are couple of options available in informatica to remove duplicate records from the source. For Relational Tables 1. Source Qualifier > ‘SELECT DISTINCT’ option 2. Source Qualifier > SQL override (Write your own Query) For Flat files or other sources Sorter > Aggregator Sorter > Expression >… Continue reading How to remove duplicate records Using Aggregator
Informatica Certification – My Notes
The below question and answers are my preparation notes for Informatica Developer Specialist certification which I am planning to take some time in June. I will continue update this page as i go through various topics as per syllabus. Just follow me, will do it together and become informatica certified specialists. 
Informatica Certification Developer Specialist
PowerCenter Data Integration 9.x: Developer, Specialist Certification This is Skill set Inventory document from Oracle website. Here you will get all the information which is required for the Informatica certification Perspective. About the Informatica Certified Professional (ICP) Program Informatica certification is a two-stage structure that correlates professional certification with the “what, why and how” of… Continue reading Informatica Certification Developer Specialist
Informatica Target Load Plan
Target Load Group and Target Load Plan If you have multiple Source Qualifier transformations connected to multiple targets, you can specify the order in which the Integration Service loads the data into the targets using ‘Target Load Plan’. This will give you an option to control which target load order group need to execute first and… Continue reading Informatica Target Load Plan
How to Load only Half of the records from Source table
Load only Half of the records from Source table The requirement is to process only half of the records from the source table. To do this we need to calculate the total number of rows (TOTAL-COUNT) in the source table and then assign ROW-COUNT and exclude the rows if the ROW-COUNT <= (TOTAL-COUNT/2). Let’s design the mapping for this. We… Continue reading How to Load only Half of the records from Source table
How to fix [Informatica][ODBC Oracle Wire Protocol Driver] Optional feature not implemented
[Informatica][ODBC Oracle Wire Protocol Driver] Optional feature not implemented You may get this error while trying to Preview data on Target in a Mapping Designer. I have also seen the same error from Source Qualifier Transformation while applying SQ properties. Let’s see how to fix this error. This is a part of ODBC Data Source configuration,… Continue reading How to fix [Informatica][ODBC Oracle Wire Protocol Driver] Optional feature not implemented
Source Qualifier – Full Outer Join
Full Outer Join using Source Qualifier – ‘SQL Query’ Integration service support only Left & Right outer joins. We cannot perform Full outer join using ‘User Defined Join’ properties in SQ, you will get a database error. This can be done by using overriding default ‘SQL query‘ in SQ. Full Outer Join Query SELECT EMP_JOB1.JOB_ID,… Continue reading Source Qualifier – Full Outer Join
Source Qualifier – User Defined Join – Right Outer Join
How to join two tables – Source Qualifier – User Defined Join – Right Outer ** Source Qualifier Transformation is the most efficient way to join two tables in the same Database ** There are two options available in SQ to join multiple tables, One using ‘User Defined join’ property and the other using ‘SQL Query’. We can… Continue reading Source Qualifier – User Defined Join – Right Outer Join
Source Qualifier – User Defined Join – Left Outer Join
How to join two tables – Source Qualifier – User Defined Join – Left Outer ** Source Qualifier Transformation is the most efficient way to join two tables in the same Database ** There are two options available in SQ to join multiple tables, One using ‘User Defined join’ property and the other using ‘SQL Query’. We can… Continue reading Source Qualifier – User Defined Join – Left Outer Join
Source Qualifier – User Defined Join – Normal Join
How to join two tables – Source Qualifier – User Defined Join ** Source Qualifier Transformation is the most efficient way to join two tables in the same Database ** There are two options available in SQ to join multiple tables, One using ‘User Defined join’property and the other using ‘SQL Query’. We can create a join override… Continue reading Source Qualifier – User Defined Join – Normal Join
Joiner Transformation – Normal Join
Joiner Transformation – Normal Join Let’s create a Mapping with a Joiner Transformation – Normal Join. We are going to join two tables JOB_ID1 & JOB_ID2 and create a target with the ports from two tables. Normal Join With a normal join, the Integration Service discards all rows of data from the master and detail… Continue reading Joiner Transformation – Normal Join
Joiner Transformation Overview
Joiner Transformation Overview Use the Joiner transformation to join source data from two related heterogeneous sources residing in different locations or file systems. You can also join data from the same source. The Joiner transformation joins sources with at least one matching column. The Joiner transformation uses a condition that matches one or more pairs… Continue reading Joiner Transformation Overview
Mapping with Sorter Transformation
Mapping with Sorter Transformation # To sort data in ascending or descending order. # Can sort data from relational or flat file sources. # The Sorter transformation contains only input/output ports. # All data passing through the Sorter transformation is sorted according to a sort key. # The sort key is one or more ports… Continue reading Mapping with Sorter Transformation
Aggregator Transformation Overview
Aggregator Transformation Overview The Aggregator transformation performs aggregate calculations, such as average, Sum, Max & Min etc. The Aggregator is an active transformation that changes the number of rows in the pipeline. The Integration Service performs the aggregate calculation on groups. The Integration Service then passes the last row received, along with the results of the aggregation.… Continue reading Aggregator Transformation Overview