SSIS Union All Transformation

SSIS Union All Transformation

Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in 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 this Wiki article.

Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Zoltán Horváth edited Original. Comment: a bit of formatting

Page 1 of 1 (1 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Zoltán Horváth edited Original. Comment: a bit of formatting

  • Zoltán Horváth edited Revision 1. Comment: rephrasing

  • Zoltán Horváth edited Revision 2. Comment: adding link to SSIS Data Flow Tasks

  • Zoltán Horváth edited Revision 3. Comment: clarifying buffer usage

  • Zoltán Horváth edited Revision 4. Comment: adding a simple usability case

Page 1 of 1 (5 items)