In this SSIS Tutorial I will show you how to use Union All Transformation. For the purpose of this tutorial I will use SSIS 2012
Other Versions of SSIS:
- SSIS 2012
- This video has been created using SSIS 2012
- SSIS 2008
- The examples we used should work in SSIS 2008
- SSIS 2005
- We suspect that this will work in SSIS 2005 as well
The Union All Transformation allows us to combine multiple source inputs into one output.
I have got SSIS Package with two OLE DB Sources and connection to AdventureWorks2012.
In first source I have got FirstName,Gender (which shows M for Male) and EmployeeKey.
And in second source I have got the same fields but gender shows F for Female value.
My task is to combine them together so 5 Males and 4 Females so I should get 9 rows.
In order to do that I will use Union All Transformation. I create data paths and double click on Union All to open Editor.
Union All Transformation contains Output Column Name which is the Column Name we will get after the transformation. We also have Union All Input 1 and Union All Input 2 which is the data paths I created. SSIS already maps the column names as they are exactly the same so it's easy in this case.
I add derived column, created data path and enabled data viewer and run the package to preview the results.
So we received 9 rows.
In this extra example I will show you how to map fields with different names.
I copy the Source (I add the source to Union All creating data path) and go to Editor.
You can see that Union All Input 3 was added and the fields was mapped automaticity.
I delete this source and copy again but without creating data path. I go to that source and I change Field Names. So FirstName will be displayed as Name and EmployeeKey as EmployeeID.
I re-create data path to union from my new source and you can see that FirstName and EmployeeKey are not mapped with fields from Input 4.
I select Name for FirstName field and EmployeeID for EmployeeKey.
I run the package and in Data Viewer we can see the all input sources where combine correctly.
My package was run successfully and I got 13 rows.
In this example I will show you what happens when we don't map the fields. I will remove the last source and I will create new one but in this case I remove EmployeeKey and then I create the path to Union All.
In Union All Input 4 say that one field is ignore but I will also change to ignore in Union All Input 1.
And let see what happens during the preview. We don't have a field so we get Nulls.
So that is the behavior we should expect from SSIS.
In this Example I will remove Output Column Name. I go to Union All and in the Output Column Name I change FirstName to Name and EmployeeKey to EmployeeID.
So We have the same output but in this case we have Name and EmployeeID column names which we updated in the UNION ALL editor.
So that is how we rename the columns.
I hope that helps