Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 26

Combining Multiple Inputs with the Union All Transform

The Union All Transform combines multiple inputs in the Data Flow into a single output rowset. It is very similar to the Merge Transform, but does not require the input data to be sorted. For example, in Figure 26-1, three different transforms are combined into a single output using the Union All Transform. The transformation inputs are added to the output one after the other; thus, no rows are reordered.

Figure 26-1

To configure this transform, bring the blue precedence constraints from the sources or transformations you want to combine to the Union All Transform. SSIS automatically maps the columns if they have the same name, but if you want to verify that the columns are correctly mapped, open the Union All Transformation Editor. The only time you must open the Union All Transformation Editor is if the column names from the different inputs do not match. During development, if upstream components get tweaked or something else changes to disrupt the column mappings of the Union All Transform, those mappings must be fixed manually.

The transform fixes minor metadata issues. For example, if you have one input that is a 20-character string and a different input that is a 50-character string, the output of this from the Union All Transform will be the longer 50-character column. Occasionally though, when you make ...