SSIS- Union All Transform

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.

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

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.

Finally execute the package and verify the result.