Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 22. 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 22-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 22-1. Figure 22-1

To configure this transform, bring the green 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 the columns are correctly mapped, open the Union All Transformation Editor. The only time you really 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 will have to 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 one that is 50 characters, the output of this from the Union All Transform will be the longer 50-character column. Occasionally though, when you ...