Merge Data by Using the Union All Transformation
To add and configure a Union All transformation, the package must already include at least one Data Flow task and two data sources.
The Union All transformation combines multiple inputs. The first input that is connected to the transformation is the reference input, and the inputs connected subsequently are the secondary inputs. The output includes the columns in the reference input.
To combine inputs in a data flow
In SQL Server Data Tools (SSDT), double-click the package in Solution Explorer to open the package in SSIS Designer, and then click the Data Flow tab.
From the Toolbox, drag the Union All transformation to the design surface of the Data Flow tab.
Connect the Union All transformation to the data flow by dragging a connector from the data source or a previous transformation to the Union All transformation.
Double-click the Union All transformation.
In the Union All Transformation Editor, map a column from an input to a column in the Output Column Name list by clicking a row and then selecting a column in the input list. Select <ignore> in the input list to skip mapping the column.
Note
The mapping between two columns requires that the metadata of the columns match.
Note
Columns in a secondary input that are not mapped to reference columns are set to null values in the output.
Optionally, modify the names of columns in the Output Column Name column.
Repeat steps 5 and 6 for each column in each input.
Click OK.
To save the updated package, click Save Selected Items on the File menu.