SSIS Union All Transformation

The Union All transformation is used to combine all the inputs into one output and the columns in the inputs must be mapped to columns in the output. Ideally all the inputs have the same number of columns with the same data type. If not you will have to make sure at least one column in the inputs mapping to the column in the output and there is no conflict among the data types of the mapping columns.

In the upcoming example, I'm going to get a copy of the data Conversion package, use the same Excel file as a data source and add a new data source from a flat file. Then we'll use the Union All transformation to combine the 2 inputs into one output. Let's check the Flat file first.

The file Books2.txt was saved in C:\SSIS folder and we added additional column "Bought Store" for testing purpose. Take the following steps to test the transform.

1. Make a copy of the package DataConversion.dtsx and rename the copy to UnionAll.dtsx in the project LearnSSIS2 which created in the previous section.


2. Double click the new package to open it and create a Flat File Connection Manager to point to the flat file we displayed above.

The default data type of all the columns are string type.



3. Change the data type of the Price column to DT_R8 and the type of Bought Date to DT_DATE in the Advanced tab in Flat File Connection Manager Editor.





4. Click OK button to close the editor and then click Data Flow tab to go to data flow edit area.


5. Remove the output from the "Data Conversion" transform and drag & drop the "Union All" transformation and Flat File Source to the data flow edit area and link them with the other transforms as shown below.





6. Right-click the Flat File Source and choose "Edit..." to open the editor and select the Flat File Connection Manager as its connection manager. Click Columns tab, the columns looks like below.





7. Click OK button. Then right-click Union All transform and choose "Edit" to open the editor. Then change the setting in Union All Input 2 as shown below.



There is no column in Input 2 to map to the output column "Book Name" because it was converted to Copy of Book Name with string type. You can add a "store" column at the end of the mapping list if you want.



8. Click OK button and Right-click OLE DB Destination to open its editor and click Mappings tab to make sure the mappings of columns are correctly.





9. Click OK and add data viewer between Union All and OLE DB Destination and then run the package.



The first 3 records in the data viewer are the data from the flat file and the others are from the Excel file. No record was removed even if the record had the same data as the other's.