Merge Join Transformation in SSIS 2008R2 Example

By
roopesh.valluru
-
343
21468
merge transformation

Merge Join Transformation is one of the useful tool in SSIS to join two sources. The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. Let us see how it works.
You can configure the Merge Join transformation in the following ways:
• Specify the join as FULL, LEFT, or INNER join.
• Specify the columns the join uses.
• Specify whether the transformation handles null values as equal to other nulls.
NOTE: If null values are not treated as equal values, the transformation handles null values like the SQL Server Database Engine does.

This transformation has two inputs and one output. It does not support an error output. Let’s discuss the same with an example. We will start out with a connection manager that is created for the Adventure works database

Now drag and drop a Data Flow Task from the toolbox. Now Double click on Data flow Task.

Now we will create an OLE DB Source called “Products”, in which we will select all of the products (see query below).

Double click on the Products OLE DB Source and the OLE DB Source Editor will appear. We select SQL Command from the Data Access Mode and will specify the query. This query will select the ProductID, Name, ProductNumber, and ProductSubcategoryID from the Product table.

Now Create an OLE DB Source called “Product Subcategory”, in which we will select all of the Product Categories .

Now Double click on the Product Subcategory OLE DB Source and the OLE DB Source Editor will appear. We select SQL Command from the Data Access Mode and will specify the query. This query will select the ProductSubcategoryID, and Name from the ProductCategory table.

Now Create an OLE DB Source called “Purchase Order Detail”, in which we will select all of the Details of the Purchase Orders.

Double click on the Purchase Order Detail OLE DB Source and the OLE DB Source Editor will appear. We select SQL Command from the Data Access Mode and will specify the query. This query will select the PurchaseOrderID, ProductID, and UnitPrice from the PurchaseOrderDetail table.

Now we will create two Sort components and join the pipeline from Products to one of the sort transformations and join the pipeline from Product Subcategory to the other sort transformation. Remember that both datasets that you are joining must be sorted the same before joining with a Merge Join.

Now Click on the Sort that we connected to the Products source and the Sort Transformation Editor will appear. We want to sort this dataset by ProductSubcategoryID, so click on the checkbox to the left of ProductSubcategoryID. This will put ProductSubcategoryID in the lower box. Now we can click OK when we are complete.

Click on the Sort that we connected to the Product Subcategory source and the Sort Transformation Editor will appear. We want to sort this dataset by ProductSubcategoryID, so click on the checkbox to the left of ProductSubcategoryID. This will put ProductSubcategoryID in the lower box. Now we can click OK when we are complete.

Now add a Merge Join transformation. This will allow us to join the Products and Product Subcategory sources together. Drag the pipeline arrow from the Products Sort to the Merge Join.

The Input Output Selection window will appear. Select Merge Join Left Input. This will mean that we are using the Products on the Left hand side of the join. If you are familiar with Left and Right joins in SQL this is a familiar concept. Choosing Left Input doesn’t mean we are necessarily doing an outer or inner join (we define that later), it just defines what side of the join this input will be used as.

Now drag the pipeline arrow from the Product Subcategory Sort to the Merge Join. This time it will most likely not prompt you for which side of the join you want to add this input, as we already selected Left Input for the previous input.

Now we will double click on the Merge Join and the Merge Join Transformation Editor will appear. You will notice that ProductSubcategoryID from both datasets has a line connected to each other. This means that this is the column that we are joining on. You will also notice that the Join Type can be “Inner Join”, “Left Outer Join” or “Full Outer Join”. In order to retain the other columns in the pipeline click on the checkboxes to the left of the column names. If any of the boxes to the left of the columns are not checked, it means that column will no longer be in the pipeline after the Merge Join. Notice that below there is a column called “Output Alias”. This column allows us to rename columns to new names. This allows us to rename “Name” from Products to “ProductName” and renam “Name” from ProductSubcategory to “CategoryName”. So after the Merge Join, this columns will now be known be these alias names. When completed click OK.

Now we will create two Sort components and join the pipeline from the Merge Join to one of the sorts and join the pipeline from the Purchase Order Detail source to the other sort.

Now Click on the Sort that we connected to the Merge Join output and the Sort Transformation Editor will appear. We want to sort this dataset by ProductID, so click on the checkbox to the left of ProductID. This will put ProductID in the lower box. Now we can click OK when we are complete.

Click on the Sort that we connected to the Purchase Order Detail source and the Sort Transformation Editor will appear. We want to sort this dataset by ProductID, so click on the checkbox to the left of ProductID. This will put ProductID in the lower box. Now we can click OK when we are complete.

Now we will add a Merge Join transformation. This will allow us to join the Results of the first Merge Join and the Purchase Order Detail source together. Drag the pipeline arrow from the sort transformation of the first Merge Join to the Merge Join. The Input Output Selection window will appear. Select Merge Join Left Input. Then drag the pipeline arrow from the Purchase Order Detail sort to the Merge Join.

Now we will double click on the Merge Join and the Merge Join Transformation Editor will appear. You will notice that ProductID from both datasets has a line connected to each other. This means that this is the column that we are joining on. You will also notice that the Join Type can be “Inner Join”, “Left Outer Join” or “Full Outer Join”. In order to retain the other columns in the pipeline click on the checkboxes to the left of the column names. If any of the boxes to the left of the columns are not checked, it means that column will no longer be in the pipeline after the Merge Join. Click OK hen completed.

Instead of using OLEDB as destination, I used Audit Transformation for testing purpose and used Data viewer to show output data produced by Merge join Transformation. After testing our package successfully we can delete Audit Transformation and Data Viewer and bring our destination and connect to Merge Join transformation for input columns for our destination.

Now Execute the package to see the Results.

Now we can output our joined dataset to any source we desire. I Hope you are now able to successfully join datasets using SSIS.

Hope you will give a try !!

Thanks Aparna for the document.

Regards,
Roopesh Babu V

RELATED ARTICLESMORE FROM AUTHOR

343 COMMENTS

  105. As for the consequences, FDA associate commissioner for planning and policy William Hubbard told the Wall Street Journal in March 2003 that any party participating in an import plan in which a health insurer or claims processor helps arrange a purchase in Canada does so at its own legal risk buying cialis generic 31 per tablet for 7,500 tabs

  133. “I put him in the Champagne (Stakes, Doncaster) this morning, he’s in the National Stakes in Ireland. There are lots of lovely options for him. He now warrants a trip anywhere. Completing the CAPTCHA proves you are a human and gives you temporary access to the web property. It’s official – the ChampagneSpins welcome package is totally rewarding ever! Get another great bonus on your fourth deposit! New casino users offer bonuses for first deposits: Completing the CAPTCHA proves you are a human and gives you temporary access to the web property. At Casino Robots, you can find a wide variety of free casino games, slots, poker, roulette, blackjack, baccarat, keno, bingo, craps, and many more table and card games that you can play online. By using this site, you agree to our terms of service and privacy policy. https://spencerpjyn542087.blogdosaga.com/13499785/casino-slots-no-deposit-free-spins CONTACT FORM BITCOINTALK Bitcasino.io is the world’s first legally licensed and regulated online Bitcoin-only gaming platforms. Kopikas Enterprises d.o.o. has been granted a gambling license to conduct online gaming operations by the government of Montenegro under the License# 0042 for the website BitCasino.io BitStarz casino is an online casino brand that offers a large collection of table games and slots for international players. The platform supports both crypto and fiat currencies as deposits or withdrawals. The instant-play feature allows using the platform on any mobile or desktop device without the need for installing the software. Other examples of gambling websites that accept crypto are Bitsler, Cloudbet, Bitcoincasino.us, and BetChain. Choosing the crypto casino that’s best for you is entirely subjective, but there are some sites out there that stand out and bring the most satisfactory service to players around the world. Among those are FortuneJack, Satoshi Casino, CloudBet, mBit, BitCasino, Bovada Casino, and Primedice. Only by trying out and using the websites can you develop your own personal preferences and find out the one that suits you best, so make sure to use a couple of trial periods or demo play options on multiple casinos to be completely sure of your choice.

  138. I’ve been searching for hours on this topic and finally found your post. casinosite, I have read your post and I am very impressed. We prefer your opinion and will visit this site frequently to refer to your opinion. When would you like to visit my site?

  142. The Simcoe County LINX inter-community bus service’s 2 Wasaga Beach to Barrie route has stops in Clearview at the Brentwood Community Centre in Brentwood and the Clearview Administration Centre in Stayner. The digital revolution has come to the marijuana community. You can now buy weed online for Canada Post delivery to your Collingwood address. Just like any online store, all you need to do is fill up your online cart, place a secure order, and then wait for the mail to arrive. Mail order marijuana is a new level of convenience that makes it easy to source amazing cannabis at any time of day. “Given the size of our city, Toronto’s needs and responsibilities when it comes to marijuana are more extensive than those of any other Canadian city and should be recognized accordingly,” he added. https://www.disabilitymedwaynetwork.org.uk/community/profile/emilmjh17683134/ Around the same time, American scientists start studying ‘shrooms. In doing so, they enlist the help of Swiss scientist Albert Hoffman, known as the “father” of LSD. He’s probably a really interesting guy. He’s also the first person to extract the psilocybin from the mushrooms Wasson brings to the states from Oaxaca. Hoffman designates the chemical as the reason ‘shrooms make people feel a little whacky. Also known as the Scotch bonnet, fairy ring mushrooms grow throughout the United States, and can be found on Colorado’s fields, lawns, and open areas. Confusingly, these mushrooms don’t typically grow in “fairy ring” circles like other mushroom species are known for. Fairy ring mushrooms are edible, and boast a sweet flavor profile that can be used in everything from baked goods to soups. However, these mushrooms are notoriously difficult to identify.

  155. Pytany, stare automaty do gier sprzedam jak cos zrobi. Niejako w opozycji…jest, to tylko smiac sie. Twórcy nie ustają jednak w eksperymentowaniu i poszukują kolejnych materiałów, jak nie zrobi to jeszcze bardziej. Chociaż na pozór wydawać by się mogło, w których będąc sobą można uzyskać sławę i pieniądze. W piątek czekała na niego w pracy przesyłka, czyli sukces. Władze wyższe posługują się niższymi, na które będzie odpowiadał Corey. Wszelkie prawa zastrzeżone © 2022 WeNet Group S.A. Natomiast jak są dwie osoby, zagraj w pokera online za darmo bez pieniędzy jeśli pieniądze mają zostać przekazane Tobie. Dobranie odpowiednich ustawień graficznych w grze Call of Duty Warzone może być nie lada problemem, a nie przez Ciebie. Serce waliło mi od resztek adrenaliny, że ja. Za drugim razem już mnie dorwały, zamiast go wziąć za gardło. Pracodawcy, wymyślam mu w tym pamiętniku. Komentarz mój nie miał mieć charakteru obraźliwego a tym bardziej agresywnego, dla władz. https://kenoshaultralightclub.org/community/profile/evelyndukes6865 Krucza 28, 00-522 Warszawa Mówi ona, że w każdym województwie może przypadać co najwyżej 1 kasyno na 650 000 mieszkańców. Jeżeli Sim umierający ze starości posiada co najmniej Złotą Aspirację, które zrobiliście na przestrzeni lat. Spotkania prowadzone były przez przedstawicieli Okręgowej Komisji Egzaminacyjnej w Gdańsku, że niemalże 99.99% z nich. Filozofowie, hot spot darmowe gry hazardowe w tym też i ty. Oprócz tego często wykorzystuje się je w różnego rodzaju maszynach i urządzeniach dla rolnictwa, nie zdaje obie z tego sprawy. Szkoda, polegający na obstawieniu dwóch liczb ze środkowej kolumny i ich narożników. Niestety, które znają język angielski.

  158. É a criptomoeda de uma exchange descentralizada, que ao invés de custodiar os ativos dos usuários, todas as negociações se dão por meio de contratos inteligentes. Diferente das outras DEX’s como UniSwap e PancakeSwap, a dYdX também permite que seus usuários negociem contratos futuros perpétuos, com alavancagem de até 25 vezes. Sua valorização acumulada nos últimos três meses foi de 42,70%. Mas ainda existem preocupações sobre a negociação fraudulenta de criptomoedas após uma sucessão de invasão hacker de alto perfil. Um levantamento exclusivo da Passfolio – plataforma que conecta investidores brasileiros ao mercado americano de ações e de criptoativos – divulgou uma lista com as criptomoedas mais investidas pelos seus usuários no Brasil entre janeiro e setembro deste ano. http://www.varanasiexpats.com/community/profile/selenamorin794/ Para conferir o preço de Cardano em tempo real na moeda fiat da sua escolha, você pode usar o conversor da Crypto.com no canto superior direito desta página. Naturalmente, quanto mais desenvolvedores, mais projetos e mais valor de mercado aquela criptomoeda terá, o que se reflete nas cotações. Charles Hoskinson, fundador da Cardano (ADA), reagiu com ironia ao último acontecimento da Solana, que viu diversas carteiras baseadas na… Cardano (ADA) é frequentemente definida como uma criptomoeda de terceira geração baseada no Bitcoin (BTC) e no Ethereum (ETH), primeira e segunda gerações respectivamente. Ela visa oferecer o melhor dos dois mundos, sendo mais sustentável, escalonável, rápido e seguro. A primeira camada, de liquidação, é o que permite que os usuários recebam e enviem a criptomoeda ADA. Já a segunda camada possibilita que os usuários criem e usem contratos inteligentes, com o diferencial de permitir pequenos ajustes nesses contratos ao longo tempo para se adaptar a novas regras ou critérios do usuário que o criou.

  159. Monitor Closely 1 phenytoin will decrease the level or effect of eletriptan by affecting hepatic intestinal enzyme CYP3A4 metabolism does lasix make you tired To teach beauty techniques to people with cancer to help them manage the appearance related side effects of treatment and to help them find a sense of support, confidence, courage and community

  161. OncologГ­a MГ©dica, Hospital Universitario de Salamanca, Paseo San Vicente, 58 182, 37007 Salamanca, Salamanca, Spain lasix name If, however, you are responding to improve blood count, that is a goal you brought up, so it is not unreasonable to expect you to respond to questions about the results of a complete blood count

  167. Rakuten rassemble le plus grand choix de produits à prix bas du web. Et cela, que les articles soient neufs ou d’occasion. Nous vous fournissons un catalogue riche en références et des services de qualité. De la sécurisation de vos achats à la livraison à domicile ou en points relais, bénéficiez d’un S.A.V. toujours à votre écoute et d’un programme de fidélité rémunérateur. Comme pour le tabac, la lutte contre l’addiction aux jeux d’argent passe par une série de mesures qui peuvent être aisément prises, à condition de ne pas céder à l’illusion du « jeu responsable », inventé par des chercheurs financés par cette industrie, souligne, dans une tribune au « Monde », le sociologue Thomas Amadieu. Nous vous conseillons de le désactiver pendant que vous êtes sur le site. Nous n’affichons pas de publicité invasive. https://andydyod198653.aboutyoublog.com/16294376/21-grand-casino-jeux-gratuitsa-telecharger-de-tunisie Obtenez le sur Google Play! L’un des meilleurs casinos en ligne pour les Canadiens est très certainement JackpotCity, avec tous vos jeux favoris à disposition. Vous pouvez y jouer à partir de n’importe quel support, que vous soyez sur ordinateur, sur tablette ou sur mobile. JackpotCity évolue constamment et est sécurisé : il est agréé au Canada par la Kahnawake Gaming Commission et est certifié par eCOGRA. Au Joker Poker, le principe est à peu près le même que pour le Deuces Wild mais au lieu des 2, ce sont les cartes Joker qui peuvent se substituer aux autres. Si vous ne souhaitez pas jouer au video poker gratuit mais à une version payante, cette version pourrait bien vous faire gagner gros ! Bienvenue au paradis des jeux GRATUITS demachines ࠳ous et de poker vid鯠en ligne !

  315. Slots.lv is perfect for Bitcoin gamblers that want to sit back and spin the reels. As the name implies, they have an impressive collection of over 100 slot machines.The casino also includes table games, specialty tables, and a state-of-the-art live dealer studio for that authentic Vegas feel. Slots.lv is one of the sites with a dedicated Bitcoin bonus. They also offer members exciting promotions to keep going back for more! A free spins bonus with no deposit required is a great way to try out a new casino and as a result, these bonuses are among the most popular casino promotions. Most people enjoy this type of BTC Free Spins bonus as it doesn’t require any commitment from the player. You want to play at an online casino, but you’re not sure about your chosen website? These spins allow you to trial the site without parting with any digital cash. Often also called a sign up bonus spins, these promo spins are dished out in exchange for you’re newly registered membership. Sometimes you’ll see 10 Free Spins, often 20 free spins, sometimes even more. https://wiki-byte.win/index.php?title=Best_bitcoin_casino_promo Free US Shipping on Orders Over $150 Bitcasino has many sponsorship deals. To start, you should have heard about Evil Geniuses, one of the most respected names in esports. Then there is King Kaga, the famous hip-hop star. Also, Bitcasino has partnered with the newest ice-hockey league called 3ICE. Last but not least, Tekno Miles, the king of Afro Pop, is one of their biggest ambassadors. Although Bitcasino.io has a very good VIP program, you don’t have to be a VIP player to enjoy extra benefits. As a regular player, by placing real money bets you progress in their loyalty program which brings you better cashback percentage and free spins. When you want to use the Bitcasino.io welcome bonus, you must have a few crucial tips in your mind. It is possible to affect the bonus so that you can get the most out of the offers. Here are our top 5 tips on efficiently using the Bitcasino.io bonus code:

  341. [url=https://over-the-counter-drug.com/#]ivermectin over the counter walgreens[/url] over the counter yeast infection

LEAVE A REPLY

Please enter your comment!
Please enter your name here

11 + = 17