MERGE Transformation in SSIS with Example

19
32131

Friends,

In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two sorted dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations.

  • The transformation inputs are not sorted
  • The combined output does not need to be sorted.
  • The transformation has more than two inputs.
MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example.
I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
  • Open a new project and drag a Data Flow task from toolbox in Control Flow.
  • Edit the Data Flow task by double clicking the object or by selecting EDIT button on Right click on the object. Make sure the Data Flow Page is opened as shown below.

  • Select TWO OLE DB data sources from data flow sources and drag and drop it in the data flow.
  • Double click on the OLE DB data source to open a new window where we can set the properties of the connection.
  • Select the connection manager and click on new button to set the connection string to the TWO tables as shown below.
  • Set the connecteions to the TWO tables created using TWO OLE DB data sources.
  • As said earlier, we need to SORT the data before giving as input to the MERGE transformation and hence drag and drop TWO SORT transformations into Data Flow pane and provide output of each OLE DB source to each Sort transformation as shown below.
  • Open the SORT configuration and select the Checkbox as EmpNo which means the SORT will happen on EmoNo column a s shown below and apply the same in both the SORT transformations. Also provide sort type as either Ascending or Descending as per your requirement.
  • The Input data from both the sources is SORTED now and hence add MERGE transformation to the pane and provide OUTPUT of both sort transformations as input to MERGE transformation as shown below.
  • Now drag and drop Flat File Destination to see the output on file, make connection between the Merge and the Flat File Destination as shown above.
  • Double click on the Flat File Destination to configure, In CONNECTION MANAGER select the NEW Option to set file path of the file and Click OK. If you select a file which already Exists then it will take that file else a NEW file will be created.
  • Check the mappings between Available Input Columns and Available Destination Columns and click OK.
  • Now the Package is ready which pulls the data from TWO tables then sorts and then mergers the data that is coming from two sources before copying to destination file. Trigger the packages and make sure all turns to GREEN.
  • Now open the file and see the data copied into the file which is coming from TWO sources.
In the above example I have taken OLE DB sources and Flat file destination to explain MERGE transformation. You can use any SOURCE and DESTINATION types depending on your requirements. The key things to keep in mind while using MERGE transformation –
  • Same number of columns should be there in both the sources.
  • Same data types should be present for mapping columns.
  • Data should be sorted before giving as input to MERGE.
That’s it .. as simple as this .. Try and Enjoy .. 🙂

 

Regards,
Roopesh Babu V

 

19 COMMENTS

  1. Play free slot games online not for fun only but for real money rewards too. Most free casino slots for fun are colorful and visually appealing, so about 20% of players play for fun and then for real money. The best free online slots are exciting as they’re entirely risk-free. Enjoy basic 3-reeled titles or complicated new modern versions for your taste: Enjoy our growing list of free slots to play for fun with no download or registration required. We’ve selected some of the most popular slots that you can find at most online and brick-and-mortar casinos so you can get real experience of playing authentic casino slots at your finger tips for free. Here at Casino.org we rate the best free slots games, and offer a selection of unbeatable free online slot machines for you to play right now – just take a look through our games list. Once you find one that takes your fancy, you could be up and running within minutes.
    http://www.thinkbread.co.kr/bbs/board.php?bo_table=free&wr_id=298640
    There really aren’t any mobile casinos better than BetMGM. The range of BetMGM casino games is among the best, and the layout is immaculate. Any online casino lover’s first concern is to find their favorite game in an online casino. So, it is also our first care to evaluate iPhone and Android casino apps and the range of games they offer. Though every mobile casino app includes slot games, the number of their offering could vary from a few dozen to hundreds. If you love to play slots, you should consider Ignition or Las Atlantis, which extend a huge range of classic, video, and jackpot slot games. Meanwhile, you can access a “Download” casino using an app that you download from the casino site or the app store of your choice. Specific casinos pride themselves on their real mobile-friendly casino, such as Jackpot Village casino, which has crowned itself the best mobile casino and has the awards to prove it. Naturally, some people will wonder why it’s even necessary to download casino software. Especially when so many platforms provide their games in an instant-play scenario today. Well, there are some benefits to downloading the software as an alternative, including their welcome bonus and free spins.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

89 − = 85