Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
Login
New Account?
Recovery
Go to Login
SSIS

SSIS- Union All Transform

Updated on Sep 30, 2020

This is a Fast Forward tutorial on Union All Transformation in Microsoft SQL Server Integration Services.

Union All

The Union All transformation combines data from multiple inputs into one output. This transformation has multiple inputs and only one output. It does not support an error output. The metadata of the columns from multiple inputs must match. Union All is similar to union all set operation as in databases.

Implementation

Let us create a Data Flow. Let us take try to use the Union All transformation to stage the employee information from three department locations coming as flat files. Here we want to stage the flat file data of employee details to a target table.

Control Flow
Control Flow

Here we have three Flat File Source followed by Union All transform and finally OLEDB Destination target table.

Data Flow
Data Flow

Connect the inputs from the flat file sources to the Union All transformation. The inputs should have the exact number of columns and datatypes. Input column alias name can differ. Also we can change the Output Column Name alias. Double-click the transformation to open the editor and define the output mapping.

Union All Split Transformation Editor
Union All Split Transformation Editor
Union All Split Transformation Properties
Union All Split Transformation Properties

Finally execute the package and verify the result.