Union transformation

The Union transformation is an active and Connected transformation.

The Union transformation is a multiple input group transformation that you can use to merge data from multiple pipelines or pipeline branches into one pipeline branch. It merges data from multiple sources similar to the UNION ALL in SQL statement to combine the results from two or more SQL statements.

Points to remember:

• We can create multiple input groups, but only one output group.
• We can connect heterogeneous sources to a Union transformation.
• All input groups and the output group must have matching ports. The precision, datatype, and scale must be identical across all groups.
• The Union transformation does not remove duplicate rows. To remove duplicate rows, we must add another transformation such as a Router or Filter transformation.
• We cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.

Working with Union transformation:

1. Import/ create soure and target.
2. In the mapping drag source, target, and create Union transformation.
3. Edit Union Transformation. Go to Groups Tab
4. One group will be already there as we dragged ports from Source to Union Transformation.
5. As we have n source tables, we need n input groups. Click Add button to add more groups.
6. We can also modify ports in ports tab.
7. Click Apply -> Ok.
8. Drag target table now.
9. Connect the output ports from Union to target table.
10. Validate mapping.
11. Create Session and Workflow. Run the workflow

Question: I have different flatfiles with same structure presented in different locations, and I need to merge data vertically. Then how many Union transformations I need?
Ans: In this particular scenario, we use "Indirect loading" at session level which will discuss in next posts.

Note: If the source tables are relational tables then we can write Sql query (Union) at Source Qualifier transformation for best result (in case of more source tables).