SSIS is all about collecting data. And one of the things you know you'd like to do is collect data and merge them. For example, you can see on the screen, you know, probably you can get data from two files, like five will file one dot txt and file two dot txt. And then you would like to merge these two files and create one file, you know, which has merged data from these two sources. And it also possible that, you know, these sources can be of different types, you know, for example, you know, one data you are getting into a text file from a text file, and the other data you're getting from a table. Right?

So how can we do this? So that's possible in SSIS by using merge and merge joints. So in this video, we'll talk about how to use merge and merge joints to combine data from different data sources and create a merged data. So in order to demonstrate merge, you know, what we'll do is that you can see that we have two files here. In the folder. So one is file one dot txt, which has two records, like shave and rajjo.

And it also has Amount value. And the same way we have file two dot txt. So what we'll do is let's go and create a simple SSIS package, which will go and merge both file one and file two dot txt into one file and give us a merge record set. So here's a simple project I've created a size project with a name called as combined data. So let's go ahead first in the toolbox. And let's drag and drop a data flow task here.

And let's give it a name as much. Let's go inside the merge. So the first thing is we'll go and extract these two flat file source right and these two flat file source will be provided as the input to the merge. Okay. And then finally, we will go and create a simple text file as output. So the First thing is, you know, this control here will read the file one dot txt, this control will read the file to dot txt, they will provide the input to the merge component, and the component will merge and provide out the flat file destination.

So let's first go and provide the file one input here. So let's click on Edit. And let's click on new. Currently, the files are located in D Drive files folder. So I'll copy this address. So this one will take file one.

Browse Ctrl V. So that is file one input that the column names are the first row is having column names. So the preview looks fine here says OK OK, okay, there's already a connection called as file one. So I'll just a file one txt. Okay, so this controller is now pointing towards file one the let's, let's let's go to the next control here. Let's again click on new says file to txt file to this take the file to as the input all this looks fine preview yes okay right and now the next thing is you'd like to go and provide output of both of these files to the merge component. So now let's take the file one dot txt and give it as an input to the merge component.

Now the time we do that, what he says is that okay, this input where you're giving right should be merged with what? So I'll say that okay, this is input one and let it be merged with input two. Okay and the input to will come from here. Great now Let's go and edit the merge component. Now, that's a very expected error. If you see the error says that, you know, the sort key positions of both the inputs are not organized properly.

In other words, the merge component, you know, expects that the inputs which come to the merge component should be sorted. So let's go ahead and sort the output from both flat file sources here. So let's right click, go to Advanced editor. nodes, let's go to input and output properties. And we'll say that yes, we want that you know, these output should be sorted. So we'll make it true.

And we'll also define the sort key position so we'll see that Okay, the first sort, you know, should be on the name and the second shot, the the second sorting should be done on the Mount. Okay. In the same way, let's go ahead with the second input as well. So let's start this and, yet also, let's keep the sequence in the same way. Right. Now let's go to the merge component.

Let's do edit. Yes. So you can see now that he's telling us that Okay, the first input merging one has to freeze your name and amount, and the first sorting will be done on the name. And the second sorting will be done on the Mt. Then we again have one more input two, which comes from file two dot txt again it has name as well as amount, and our columns name will be also name and amount. So we are okay with this.

And this output, I will give it to the give it to a flat file destination. So let's go ahead and let's create a delimited text file here, which will be the final output. So we'll give a name as merged output. And we'll create a file here called as March 1 row in the column names. These are all fine sticks. These are all these All things So, and the mappings also looks good.

So, in other words, you know the combined output you know what is available will be name and amount, so that is fine and in the destination we want the same column names so even that is fine right. Now let's go and run this. So two records from 500. txt and two records from file two dot txt will get merged. And you can see that it has combined into one file called as merge which which it has created. So if you go now here, you know, this fi is a combination of file two, and file one. So if you see very closely if you watch closely here, this is file one dot txt, this is file two dot txt, and both of the files are now merged into one file called as monster dot txt.

So, the mirth component you know, helps To go and merge, you know, two outputs, you know from two different sources. Now remember that these sources can be heterogeneous sources. In other words, you know, one input can be a flat file source. And the other input can be SQL Server table. Okay? So the most competent actually takes two inputs and merges them and creates a, you know, creates one big record set, which is a combination of both the inputs.

Now, the next component to be discussed is the merge joint. So, if you see here, the current component we discussed was merge. The next component is again, which is very important is a merge join. Now, many times what happens is, you know, you don't want to just do a merge, you know, but you want to do a merge with some lookups. Okay, so for example, let me just go to my folder here. For instance, let's say you know, we have this file one dot txt, which has name and amount.

And let's say you have one more file here called as file three dot txt. So let's say we have one more file called file three dot txt and this file has name an address. So, in file one dot txt, I have shaved. So in this file, I will have shaved and shave, you know, stays in Mumbai, I have Raja who stays in Nepal. Okay. Now what I would like to do is I would like to go and merge these two files, you know, but I would like to merge them you know, with using proper lookups.

In other words, I would like to say that, okay, go and merge these two files, but take the amount fee from this file, one dot txt, and take the address field, you know, from this file to dot file three dot txt, and basically go and merge them, you know, by using the name as a as a lookup or the name as join. So, what you'd like to do is that you'd like to go and use this name to go and do the lookup and merge, you know, these two columns and create a new record set. Okay? So for these kind of conditions, you know, very want to go out merge with a specific condition, you will use merge joints. Okay. So what I'll do is, let's again go back to our data flow here.

And let's go and delete all this. So we keep this flat file source as it is. But now this flat file source will now go and point towards the file three dot txt in a with which we want to do the join. Okay, so I can do edit here. And let me just create a new file Connection Manager. So that is file three.

Txt. So let me just take the file three dot txt. The first row is the column names and all these things are fine. Okay. And now, also, one more thing is as we said, you know, for merge, we have to ensure that you always go and sorted so I'm going to say yes, this is sorted through that's already there. This is sorted.

Position one this is sought position two must be held escape. Okay, so this was already sorted. Now let me go and take my merge join here and let's give the input of file one here and we say yes please go and merge you know this input you know with the right input and I will as well as give the second file output output here now, you can see that he's telling us okay, what is the join key so I'll say the join key is one is the name and not the amount so I'm going to go and delete this second join key from your summary to see yes, the join key is only on the only on the name and I'll say that you know, go and create a new file here you know, which will have name which will have address and which will have amount okay.

And we only take those records you know, which are matching In other words, you know, if suppose, you know, in your file three dot txt if if Have some record like let's say you know I'll say correct you know from Mumbai then no do not go and pick up that record so you can see that you know 500 txt we have two records with a Shiv and Raja, but you know, we don't have any entry of correct. So, what will happen now is you know the correct value will not be displayed because we are doing a lookup and or the join key is the name okay. So the inner join it will only go and join those records which are existing in both feis. Okay. We'll talk about the other kinds of joins you know, against the left outer join and full OUTER JOIN, you know, as we go ahead in the video, for now, let's go and demonstrate inner join.

So I'm going to say okay, and this whole output, I will give it to a flat file destination here. So all the standards will say new. Let's create a new file with a which is a delimited text. I'll give a name here. See How much in us join Okay, so we'll go and browse yours. Let's give the file name as merge inner join the first row is the column names, all these are standard nice.

The mappings are also fine that we are all set. Now let's go and do a file. Now what should happen is as I said, you know, tools we come from file one dot txt three rows, we come from file two dot txt, but then he does an inner join he only finds two matched protocol inputs. So he only goes and spits out records here. Now, if you go and see the more INNER JOIN, we can see that yes just to give you a proper visual picture here, so that is file one and that is file three. So you can see that what he has done is he has done kind of inner join here.

So he first went to shave, he found that okay, the there is a there is a lookup of shave here. dot txt, it took the amount from here, it took it to the address from here and made one entry here, then is then looking to Roger, he found Roger, he made Roger entry with the address as well as amount. And then you know, he found that this correct has no entry into 500. txt, so he did not go and merge them and output okay. So the most you know, join you know, takes input from sources. And wherever the data is matching on the key right, only those data is outputted to the output file. So, in case if there are no matching records, you know, those records will not be sent out to the output file.

Okay. Now, let's talk about the other kind of joints. I feel remember, this merge joint also had something called as the left outer join. So let's talk about this. Now for that. What we'll do is let's go and modify our file one dot txt a bit.

Here. I would go and make one more entry here called a sham. Okay? Shamsi is in Mumbai sorry, this I'm on them so Sham and 200 amount. Now, what I'd like to do is I would like to go and merge this file one with file three in such a way that you know, all the records you know, which are there in the left source right should be displayed. Either they're matching or they're not matching In other words, okay, like shift and shift there is a matching record go and display that Roger and Roger does a matching record when that putt Sham has no matching record, it's fine still go and display that okay.

And leave credit for now okay. So, if I go and change this to left outer join, okay. So, what will happen is all the records you know, from the left source will be displayed irrespective, you know, there is a match or there isn't there is no match. And from the right source, we only displayed those records which are matching in case you know, there are no matching records. You know, don't display them. In other words I'm not expecting correct to be displayed.

You know, when I Do the left outer join. So we'll just do okay here. And what we'll do is we'll keep the same file for now that is merge INNER JOIN, okay? Or else let's go ahead and define a new file so that we can see a clean output. So let's go ahead and new file a new file Connection Manager. And this will name it as merge left join.

So, March left join. So let's see the output of this design that all standard mappings Yes, I'm happy with those mappings. Right? And now let's go and run this. Alright, now let's go and see what is that in the module of join. So you can see now So you can see now he's displaying shift Mumbai 100 why because she has a matching record.

He's displaying Roger Nepal 200 because Roger again has a matching record. And what's here now why is this incorrect? Okay. Let's go back to our merge join I think that is okay. Okay. Well you know, the my bad the file three dot txt is actually the left file and the file one dot txt right file okay.

If you see here this name address you know is actually the left file and the name amount is the right to file okay. So it has made a left join you know with the name amount txt file, okay, so let me put the perspective right here right. So you can see now you know, for Kirk he has not found any matching records here still has gone and merged them into the Morris left join testify. Okay. Please note you know, that the left fight The file dot txt is the left file here, okay? So that's where you're seeing the output you know, in a reverse manner, okay.

So, you can see now, again follow the code for Shiv he made an entry here, he found a record for Roger he made entry here, but from the left file, he did not find a record for correct but he still made entry here. So, a left outer join you know, when you use a merge a left outer join, it will display all the records of the it will go and merge all the records of the left source respect to the match or they do not match okay. While you know if the record sets you know, which are not matching of the right file, it will not go and merge them for example, you can see the Sham 200 You know, he has not gone ahead and merge into the merge left join txt file. So, when we use INNER JOIN when the matching records will be sent, when we use left outer join, you know, whatever is in whatever data is there in the left source irrespective you know if the match or not did not match you know, those will be seen To the output txt file, now let's talk about the full OUTER JOIN.

So let me do okay here and lets me go and again create a new text file. So I'm just go and create a full OUTER JOIN here. Browse Ctrl V fill out magenta join dot txt. First row is the column names standard standard mapping, so all fine. All right. Let's run this right.

So what do we have here now, let's go to full OUTER JOIN Okay, this is becoming better now. Now a full OUTER JOIN means you know, irrespective the records match or not, so wherever the records match, okay, show the proper merged output, wherever the record is not match. Industry show output so you can see okay, shave Mumbai Hundred matching shown Ragini pal 200 matching shown, okay curve does not have an entry of amount, fine, but still go ahead and put correct there. And you know, and display No amount, no records of charmer matching fine, but still go ahead and put the value of 200 during that I did not find any amount of, you know, forms a sham. Yeah, but still I'm going to go and make entry form out. So the full OUTER JOIN what it does is it actually goes and displays records which are matching or which are not matching.

Right. So, in this video, what we did is we talked about two things we talked about merge, and we talked about merge join. The merge is like a union and I'm sure that everybody has done a square here. So it goes and it just combines two records for remember right it goes just goes and combines two records and creates one record set, the merge left join goes and combines you know records you know from the left table, you know which are matching as well as the unmatched The inner join only displays records which are matching and the full OUTER JOIN, you know displays records which are matching as well as which are not matching. So hope that you enjoyed this video in this video we were trying to understand how can we use merge and merge joins, you know to go and merge inputs and create a most output.

Thank you very much