Share via

SSIS Union All Transformation

  • Article

[[articles:SQL Server Integration Services (SSIS)|Integration Services]] uses transformations to manipulate data during an [[articles:Extract Transform Load (ETL)|ETL]] dataflow. Transformations can be used in [[articles:SSIS Dataflow Task|Data Flow Tasks]], between data source and destination components, or other transformations.

There are situations when data from multiple but similar sources must be combined into a single data set, which must include the union of the original record sets. This is what Union All can solve.

Union All is a very basic transformation: by default, it simply puts all rows from any inputs to its single output. Of course, the Union All transformation can have multiple inputs.

This is a partially blocking transformation, because the output of Union All is copied into a new buffer and a new thread may be introduced into the data flow.

The order of the output records depends only on the speed of the inputs.

Basically, only a few changes can be made with the columns:

  • The columns can be "renamed". It just means a different column name is specified on the output. The column metadata depends on the metadata of the first dataflow path connected to this transformation, but it can be edited later.
  • The columns can be omitted. In some cases the logic might require to output less columns. It might increase performance as well (as the buffer manager won't have to handle unnecessary data).

It looks like this in the Data Flow editor:

When a column is ignored from an input, it means that there will be NULL values in the output when the given record comes from that input:

Changing the column names can be done here, by editing the values in the "Output Column Name" column in this editor.
Omitting a column from the rest of the data flow can be set by right-clicking on the corresponding row, and selecting Delete.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check [[articles:SSIS: List of Transformations|this Wiki article]].