In one of my recent blog post of SSIS Buffer Error , I have demonstrated how Union All transformation has caused error. I received a comment in the blog post, asking for the functionality of Union All task and why and how it is used. This blog post is for the same.
SSIS consists of various Data Flow Transformations, and Union All is amongst one of them. The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output (MSDN definition)
The functionality is pretty simple, as the name suggests, multiple inputs and one output, similar to the T-SQL Union All command.
Let us create a sample package using Union All.
First of all, Drag a Data Flow Task in the Control Flow. In the Data Flow task, drag two OLEDB source, or any different type of source you need.
Select the Connection and Set the Data access mode as SQL command, and write the SQL Command text as
OLEDBS Source ----SELECT Data = 'Table1'
OLEDB Source 1 ---SELECT Data = 'Table2'
Add the output of both these OLE DB Sources to Union All transformation.
Union All will automatically define its own Output Column Name, derived from the input column names. It will even automatically merge two matching input column names. If your input column names are different, you will need to manually select the column names to be merged into one. There can be any number of input column names.
This means, Union All, will create a new buffer for all the new columns, instead of using the existing buffer. Union all is partially asynchronous task, which takes time and requires memory to transfer the data into new buffer. It is always advisable to ignore Union All for more columns as, the buffer size will increase, which in turn cause the Buffer error.
Union all has no error output.
Let us connect the union all to OLE DB Destination and check the output.