 Hello friends, welcome to SSUnitech. So, she'll decide and this is continuation of SSIS tutorial. So, today we're going to discuss about lookup transformation and this is very useful transformation in SSIS. So, here we will understand basic of lookup in our upcoming videos. We will understand lookup advance. So, let's start with lookup. So, what is lookup transformation? So, lookup is a very useful transformation and it is used to perform lookup operations by connecting input values with data tables or data sets. Basically, it compares source data with our existing table data and filter the matching and un-matching rows. So, we can say in our lookup transformation, mainly it will return two outputs, first for the matching rows and second for the un-matching rows. When we talk about a reference data set, then we can use two types of connection managers inside the reference data set. First is LEDB connection manager and second is CASA connection manager. And we have discussed about CASA transformation in our last video. If you haven't watched the last video, so I would strongly recommend to watch that video before going forward. So, lookup transformation tries to perform an equijoint between the values transformation input and values in the reference data set. As we can see, we have a flat file and it has comma delimited file and it contains the information of sale. So, as we can see item description, quantity, value and sale percent. So, it has total four columns. Go to on SSMS. So, this is our reference table. Now, we can execute. So, here we can see it contains the information of item codes. It has item code and item description. So, in our scenario, we need to compare between item description in our flat file and then we need to compare item description in our reference table. If it will match, then we need to load that row in our item sale lookup basic table and it contains four columns, item code, quantity, value and sale percent name. So, before creating packets, I would like to explain what happened when equijoint doesn't appear. So, if there is no matching row in our reference data set, it means no join occur. So, by default, it would return an error. However, we can configure the lookup transformation to redirect such rows to no match output. So, how we can handle such type of rows with no match entries? We have total four options. First is fail component and this is our default option. So, when a new row process from data flow, lookup transformation will fail if it find any non-matching row in the lookup table. In our second option, ignore failure. If a new row passes from data flow, lookup transformation continue its processing. Even it find any non-matching row in the lookup table as well. In our third option, direct rows to no match output. So, in this option, lookup transformation direct the rows that doesn't find any matching rows in the lookup table to the no match output. In real time, we use this option mostly. In our last option, redirect rows to error output. So, in this option, lookup transformation direct rows to standard error output that doesn't match any non-matching rows in the lookup table. Let's move to SSDT to create the package. So, I want to add one more package, right click on this SSIS package and new SSIS package. Then I want to rename this package with lookup basic. Then we have to use a data flow task. We can drag and drop data flow task. Then double click on this data flow task. Inside this data flow panel, we need to configure our source transformation and destination. So, our source is flat file source. So, we can drag and drop flat file source not double click on this flat file source. Then we need to select a flat file connection manager. So, we don't have any connection manager at yet. So, click on new. So, here we need to select the file name. We can browse. So, this is our file we can select and click on open. Now, go to on columns. As we can see item description quantity value and send person. So, we are getting all the column. Click on OK. Now, go to on columns. Here we want all the columns. So, we are OK with it. Now, click on OK. So, we have done our source. Now, we need to configure our transformation. And our transformation is lookup transformation. So, we can drag and drop this lookup transformation and then connect with source. Now, double click on this lookup transformation. So, here we can see we have CASA mode connection type and many more options. So, we will discuss these options in our upcoming videos. Here we can select our reference table connection manager. So, as we can see connection type. Here we have only two type of connection manager, CASA connection manager and OLEDB connection manager as we have discussed. So, this time I want to use OLEDB connection manager. Go to on connections. So, here we need to select our OLEDB connection manager. Now, click on new. So, as we have already made the connection. So, I want to use this one. Here we need to select the table. Item lookup basic table. Now, go to on columns. So, as we can see, so this is our source input column and this is our reference table column. So, we want to compare with item description with item description. So, we can connect. And we want to get the item code from reference table. So, we can select this checkbox. Now, go to on journal tab. So, here as we can see, specify how to handle the rows with no matching entries. So, as we have discussed, so by default it has fail component and we have ignore failure, redirect rows to standard error output and redirect rows to no match output. So, I want to use redirect rows to no match output. Now, click on OK. So, now we have done our lookup transformation. Now, we need to configure our destination. So, we want to load the data in our SQL server table. So, we have to use OLEDB destination. Now, we can connect with lookup. Here we have option what output we want to move in OLEDB destination. So, we want to move only match output. So, we can select and click on OK. And un-match output we want to load in our flat file. So, we can drag and drop flat file as well. And we can connect for lookup no match output. Now, we need to configure our destinations. So, first of all, I would like to configure our OLEDB destination. So, double click on this OLEDB destination. So, here we can see, we need to select the table. So, our table is item sale table. So, we can select go to one mapping. So, as we can see quantity mapped successfully. Here we have item codes. So, we can map item code with item code. Value with value. And we have sale percent as well. So, we can map with sale percent. Now, click on OK. So, we have done our OLEDB destination. Now, we need to configure our flat file destination. So, double click on this flat file destination. And here click on new. So, here we want delimited file. So, click on OK. Here we can browse, go to on destination, then lookup. We need to specify the file name. So, this is for no match. So, I want a name with no match. Now, click on open. Column names in the first row. So, we can select this checkbox. Go to on columns, item, quantity, values and sale percent. Now, click on OK. Go to on mapping. Mapping succeed. Now, click on OK. So, we have done our package. Now, we need to execute our package and check our output. So, as we can see, 64 rows should be inserted in our table and 8 rows should be inserted in our flat file. Now, stop this package and go to on SSMS and execute our selected statement. So, as we can say, 64 rows. 64 rows have been inserted successfully. Now, go to on flat file. Go to on destination, then lookup folder. Then we need to open this no match file. So, as we can see, we are getting 8 rows. So, as we can see, these item descriptions is not available in our refresh table. That's why these rows move to in our flat file. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. And don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.