Union Transformation in Informatica

· 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).

Steps:

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)

Datawarehouse Architect