✍ Kashif M ™
☎ +91 9994883085
✉ kashif7222@gmail.com


Sunday, 8 September 2013

Union Transformation in Informatica

By on September 08, 2013

· Active Transformation

· Merges similar data coming from heterogeneous (Oracle,SQl Server) or homogenous sources.

· These data can be maintained in the formof flat files.

· Union uses UNION ALL function. It does not remove duplicates.

· Multiple input groups but only one output group (opposite to Router).

· Input and Output groups must have matching ports.

· It does not generate transactions (just combining).


1. Define Source Definition EMP(Oracle) and EMP_F (Flat file e.g. .txt file)

2. Create a .txt file EMP.txt

Go to Source Analyzer workspace, select Import from File option from the Sources menu.

3. Select the required file and click on OK

Define Target

Define Mapping

Drag Sources,Target and create a Transfn. T_UNION

By default, NEWGROUP (Blue many) and OUTPUT (grey only one) are created. There are no ports inside this blank object.

Edit UNION transformation.

Go to GROUPS tab – 

Create two Input Groups and give the INPUT group name as EMP_O and EMP_F.

In the mapping designer workspace we can now see the newly created groups.

Go to Group Ports tab and click on add new ports button.

Drag the inputs from SQ to UNION EMP_O. Likewise, drag the inputs from SQ to UNION EMP_F.

Project the output ports from output group to the target

Define Session,Workflow.

Edit the Session and in the Mappings tab mention the Source File Directory C:\ and Source Filename EMP.txt.

Output (Target table is having primary key here – so the 3 duplicate records have been rejected)

If the Target Table has no Primary Key (All duplicate records will also get populated)

mkashu: Union Transformation in Informatica
Review : Kashif | Kashif
Update: September 08, 2013 | Rating: 4.5

Comment for "Union Transformation in Informatica"


  1. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge.

    Hadoop Training in Chennai


Blog Archive