Sign In | Feedback | Donate | Search
Katie and Emil Logo
Vote Yes! I like it! [+0]  |  Vote no! Dislike? [-0]
Author: by Emil Glownia

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



Union All Transformation

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.


Mapping fields with different names

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.


What happens when we don't map the fields

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.


What happens when we remove Output Column Name

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 It will help 


Upsss... Something went wrong and Google Ads on our website did not display.

Google ads sponsor FREE content on our website and without them it would not exist.

How to fix the problem?
  1. Check if the Google Search display any ads by searching the term Business Intelligence
    • If ads don't display, try different search terms and if there no ads then there might be a problem with your browser. If you made changes to your browser behaviour then undoing them will help.
    • If ads display then, refresh our page one more time, if the problem is still there than please contact us.
  2. Become 'No Ads Learner' Member or if you are already one Sign In.
Take care
Katie & Emil
Well done! It seems Google Ads display properly now on our website.

Access our website's FREE content for FREE!