 Hey guys, welcome to ssiritech. So, see in this side and this is continuation of SSIS tutorial. So, today we are going to discuss about union all transformation and what are the differences between union all and what is transformation. So, let's start with union all. So, what is union all transformation? So, union all transformation in SSIS is used to combine data from multiple sources and that source could be excel files, flat files or anything else or multiple sequence over tables and produce one output that could be stored in your destination table. Next is union all transformation doesn't follow any specific order while merging the data and sorting into all this. So, when we mainly use union all transformation, if the transformation input is not sorted as we have discussed in our last video and we have seen that much transformation needs data sorted before using it. Next is combined output doesn't need to be sorted and next is when we are having more than two inputs then we cannot use much transformation we have to use the union all transformation. So, these are the cases where we need to use our union all transformation. This is our case study as we can see we have three input seats first sheet contain the information of male, second female and third unknown. So, by using union all transformation we want to merge data between three sheets and then we want to load in our only DB destination. So, let's have a look of excel seats. So, this is our female employee seat as we can see it has four columns, employee ID, name, gender and sample. Next is for male employee again we are having same number of columns now unknown employee as we can see we have employee ID, name, gender and sample. So, all three seats contain same number of columns and data types are also same as you can see in case of employee ID we have integer in case of name we have string data in case of gender again we have string data and in case of cell V we have integer data. Now, let's have a look of our table where we want to load the data. So, this is our table as we can see it has employee ID, name, gender and sample. So, it has total four columns. So, now I am going to SSDT to implement this package. So, here I want to create one more package. So, right click on this SSIS package and new SSIS package. Then I want to rename this package with union all. This is our union all. Now, we have to use data flow task inside the control panel. So, let's drag and drop this data flow task then double click on this data flow task. Once we double click on this data flow task then it will move to data flow panel. Inside this data flow panel we have to configure our source transformation and destination. As discussed we have excel file. So, we have to use excel source. Let's drag and drop three excel source first is for male then female and then and rename this with male rename this with female and this is for unknown. So, we can rename it unknown. So, I want to set up our source for male. So, double click on this male then as we can see excel source editor window has been opened. So, click on new. So, here we need to select our excel file path. So, you can browse and go to inside union all and then I am going to select male then open. So, first row has column names. We are okay with it. Click on auto. Here we need to select our seat where data is available. So, data is available in our seat one. So, we have to select seat one then go to one column inside this column. We want all the volume. So, you can click on okay. So, as you can see error has gone. Now, double click on this female employee. Now, we need to select our female employee seat. So, this is our female employee seat. Click on okay. Now, here we need to select our excel file. So, seat one contains the data in columns click on okay. Now, we need to configure for unknown. Here we can click on new then select our unknown employee seat and click on okay. Here again we need to select our seat. So, seat one contains the information. So, we need to select seat three. Then go to one columns inside this column as we can see employee ID name, gender and cycle. So, we can click on okay. So, we have done our source. Now, we need to configure our transformation. So, our transformation is union all transformation. So, we can use union all. Drag and drop this union all transformation. Then we need to connect with male then female and then unknown. Now, double click on this union all. As we can see output column names by ID name, gender and cycle. Union all column then union all two then union all three. So, we are getting the inputs. Now, we need to set up our destination. So, our destination is across our table. So, we have to use LEDB destination. We can drag and drop this LEDB destination and connect with union all. Then double click on this LEDB destination. Here we need to set up our LEDB correction manager. So, click on new. Once we click on new, then we can see we have made the connection. So, I am going to use that one. Here we need to select our table. So, our table name is union all table. So, we can select union all table. Now, go to on mapping. So, inside this mapping, nothing succeed. So, we need to click on OK. Once we click on OK, then go to on SSMS. Now, I am going to execute. Then we can see initially we don't have any data. Now, I am going to execute these factors. Then we can see 19,951 rows should be inserted in our table. Now, go to on SSMS and execute our selected data. 19,951 records have been inserted successfully. So, next is what are the differences between union all and merge transformation. So, first is union all. So, data should be sorted order before applying merge transformation. In case of union all transformation, there is no need to be sorted data before applying union all transformation. Next is output data could be sorted in case of merge transformation. And output data will not be sorted in case of union all transformation. Next is as we have discussed, merge transformation can accept only two inputs. And in case of union all transformation, we can use more than two inputs. Next is it's bad for performance because it accept only sorted data. And in case of union all transformation, it's good for the performance as compared to merge transformation because there is no need to be sorted input. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. And press the bell icon to get the notification of our newly uploaded videos.