Union All Transformation

The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.

Inputs and Outputs

The transformation inputs are added to the transformation output one after the other; no reordering of rows occurs. If the package requires a sorted output, you should use the Merge transformation instead of the Union All transformation.

The first input that you connect to the Union All transformation is the input from which the transformation creates the transformation output. The columns in the inputs you subsequently connect to the transformation are mapped to the columns in the transformation output.

To merge inputs, you map columns in the inputs to columns in the output. A column from at least one input must be mapped to each output column. The mapping between two columns requires that the metadata of the columns match. For example, the mapped columns must have the same data type.

If the mapped columns contain string data and the output column is shorter in length than the input column, the output column is automatically increased in length to contain the input column. Input columns that are not mapped to output columns are set to null values in the output columns.

This transformation has multiple inputs and one output. It does not support an error output.

Configuration of the Union All Transformation

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set programmatically, see Common Properties.

For more information about how to set properties, click one of the following topics:

Union All Transformation Editor

Use the Union All Transformation Editor dialog box to merge several input rowsets into a single output rowset. By including the Union All transformation in a data flow, you can merge data from multiple data flows, create complex datasets by nesting Union All transformations, and re-merge rows after you correct errors in the data.

Options

Output Column Name
Type an alias for each column. The default is the name of the input column from the first (reference) input; however, you can choose any unique, descriptive name.

Union All Input 1
Select from the list of available input columns in the first (reference) input. The metadata of mapped columns must match.

Union All Input n
Select from the list of available input columns in the second and additional inputs. The metadata of mapped columns must match.

Merge Data by Using the Union All Transformation