Home > SSIS > SQL SERVER – Combine multiple dataset into single dataset using UNION ALL Transformation – SSIS

SQL SERVER – Combine multiple dataset into single dataset using UNION ALL Transformation – SSIS


In a large enterprise data might have been scattered across geographic location. But, the business data model of the organization must be similar in most of the time.

In banking or financial service IT department often receive business data from different service centers in a standard file format. But, few columns might be added in some service center to project some vital information.

So, we need a task in SSIS to accept multiple input and generate single output. But, we should have all the column values coming from the file.

Union All Transformation

Union All Transformation receives multiple input and generate single output. In case of additional column from any input source, it will add a column in the data flow and assign null value to the rest of the column other then the original input.

Scenario

I have two excel files with different columns as shown below.

Excel file1

Excel file2

I need to combine these two file and store the data into a SQL table.

Implementation

Step 1: Create a package and add a data flow task. Go to Data Flow design interface.

Step 2: Add two excel data source and connect to the respective files.

Step 3: Add a Union all task and connect source 1. Union all will get the meta data from the first data source connected to it. If you add another source that has additional column then we have to manually add it to the data flow.

In this example, I have connected 3 column data source to the union all task initially.

Step 4: Click OK. Now, execute the package.

MSDN reference.

Thanks for reading.

Advertisements
  1. Pedro Alonso
    July 2, 2011 at 12:38 pm

    Hi, which software did you do this implementation

  1. June 24, 2013 at 9:12 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: