Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

I have around 6+ years of experience in SQL Server development and in Business Intelligence area. I am working as Datawarehouse developer. My blog space includes all technical areas related to SQL Server and MSBI (SSIS, SSRS and SSAS).
Browse by Tags · View All
SSIS 7
xml 3
dynamic 3
Union All 3
SSRS 2
Data flow task 2
Buffer 2
Time 2
Date 2
varchar(max) 1

Archive · View All
February 2013 3
September 2009 3
June 2013 2
January 2013 2
November 2012 2
July 2012 2
July 2009 2
April 2013 1
December 2012 1
February 2010 1

SSIS Union All Data Flow Transformation

Feb 26 2013 12:00AM by Divya Agrawal Shah   

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.

image

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'

imageimage

Add the output of both these OLE DB Sources to Union All transformation.

image

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.

image

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.

image

image

Tags: SSIS, Union All, buffer, Data flow task


Divya Agrawal Shah
89 · 2% · 629
3
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Hi Divya,

    First of all thank you soo much for your post.And way of presentation is good.

    NOTE:Addition to this Metadata Should be the same from all the inputs.

    Thanks again.

    commented on Feb 26 2013 6:15AM
    Bala Krishna
    84 · 2% · 676
  • Thanks Bala

    commented on Feb 27 2013 2:22AM
    Divya Agrawal Shah
    89 · 2% · 629

Your Comment


Sign Up or Login to post a comment.

"SSIS Union All Data Flow Transformation" rated 5 out of 5 by 3 readers
SSIS Union All Data Flow Transformation , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]