 Hello friends, welcome to SSUnitech, so stay on this side and this is continuation of SSIA tutorial. So today we are going to discuss about lookup scenarios and this is very important when we talk about interviews. So total we have seven scenarios and we will discuss one by one. So let's start with our first scenario. So it's on equi-join. So as we can see in our source table and in our lookup table. So in our source table we have four rows and in our lookup table again we have four rows. So what would be the output for the matching and un-matching. So let's go to SSMS and these are our tables. So now we can execute and we can check. This is our source table and this is our lookup table. So we are getting the same number of records and we want to apply lookup on the view of of name. As we can see name is highlighted. So let's go to an SSGT. Here we need to create one more package. So right click on this SSIS package then new SSIS package. Then we need to rename this package with lookup scenario. Now we need to use a dataflow task then double click on this dataflow task. So inside this dataflow panel we need to configure our source, transformation and destination. So as we have SQL Server table so we have to use OLEDB source. So we can drag and drop OLEDB source then double click on this OLEDB source. Here we need to select our OLEDB connection manager. So click on new. As we have already made the connection so I am going to use that one. Click on OK. Here we need to select our source table for case one. So this is case one source table. So we can select this table. Go to on columns. So we want all the columns then click on OK. So we have done our source. Now we need to configure our transformation. So our transformation is lookup transformation. So we need to drag and drop lookup transformation. Then we need to connect with source. Now double click on this lookup transformation. Go to on connections. Now we need to select our table and this is our reference table for case one. So go to on columns. Here we need to specify the lookup column. So we want to apply lookup on the name so we can connect with name and here we can select all these rows. Now go to on general. Here we can specify how to handle rows with the no matching entries. So we want to redirect with no match output. Now click on OK. So we have done our lookup transformation. Now we need to configure our destination. So we don't want to load data any destination so we can use multicast to check the data. Then this is for lookup match output we can select then click on OK and second lookup no match output. Here we can enable our data viewer. So right click on this data viewer then enable data viewer. Now we can save our package then we can run our package. So as we can see in our no match output we are not getting any data. In our match output we are getting all the four rows. So all the four rows is redirect to our match output. So this is our case one. So in our case two as we can see more number of records in source data. So as we can see in source data we are getting four rows and in our lookup table we have only two rows. So what would be the output for the match and unmatch. Go to on ssms and this is for case two. So we can execute this query and we can see in our source table we are getting four rows. In our lookup table we are getting two rows. Go to on ssgt. Here we need to change our table. So double click on this already be source. Here we are going to check for case two. So we can select our case two. Now click on OK. Then in lookup transformation we can also change our table. Go to on connections. Here we need to select for case two reference lookup table. Now click on OK. Now we can execute and we can check. Once we execute our package then we can see in our match output we are getting two rows and in our unmatched output we are getting two rows. This is our case two. Now move to case three. So in case three as we can see more number of records in reference table. So as we can see in our source table we are getting only two rows. In our lookup table we are getting four rows. So what would be the output for match and unmatch. So go to on ssqlsover management studio and we can execute our elected statement for case three and we can check the data. In our source table we are getting two rows. In our lookup table we are getting four rows. Now go to on ssdt. Now we need to change our table in source. So this is for case three. So we can select case three source lookup table click on OK. Then double click on this lookup transformation. And here again we need to change our table. So this is for s3. So we can select case three reference table. Now click on OK. Now we can run our package and we can check our output. Once we execute then we can see in our lookup match output we are getting two rows and in our lookup unmatch output we are not going to get any data. So this is for case three. Now move to our case four. So in our case four as we can see duplicate records in source table. So go to on ssms. Now we can execute our selected statement for case four. As we can see Eric's record seems to be duplicate. Go to on ssdt. Now we need to change the table. So this time for case four. So we can select case four source table and go to on lookup transformation. And here again we need to change our table. So this is for case four reference table. Now we need to execute our package and we need to check our output. So here as we can see we are getting the four rows which we have in our source. So if we have the duplicate values in our source then lookup transformation will redirect that duplicate values. So we can stop this package go to on case five. So in our case five as we can see duplicate records in reference data set. So as we can see in our source data we are getting only three rows and in our lookup table we are getting four rows and Eric's record seems to be duplicate. So what would be the output for match output and unmatch output. So let's go to on ssms. Now we can execute our selected statement for case five. Here we can see in our source table we are getting three rows and in our lookup table we are getting four rows. Now go to on ssdt. Now we need to change our table at source. So this is for case five. So we can select case five source lookup table click on OK. Now double click on this lookup transformation. Here we need to change table as well. So go to on connections and change for case five refresh lookup table now click on OK. Now we need to execute our package. So we can click on start. And here we can see we are getting only three rows. So if we have the duplicate values in our refresh data set then it will return only distinct values from our source. If we have duplicate values in source then it will return the duplicate value. Now we can stop our package and go to our case six. So in our case six this is for cases two as we can see in our source we are getting total four rows and in our lookup table we are getting four rows. In our source as we can see Eric's record in uppercase but in our reference lookup table we don't have in uppercase. So what would be the output in our matched output and unmatched output. So let's go to on ssms. Now we can execute our selected statement for case six. So as we can see we have total four rows in our source and in our lookup refresh table we have total four rows. Now go to on ssdt so here we need to change the table. So double click on this already be source and then we can select the table. So this is for case six. So we can select case six source table now click on OK. Now double click on this lookup transformation then go to on connection. And here again we need to change the table. So this is for case six. So we can select case six lookup table. Now click on OK. Now we need to execute our package. So as we can see Eric's record moved to unmatched output. So we can say that lookup transformation treats as a case six too. Now we need to change the mode. So double click on this lookup transformation then I want to change the case mode. This time parcel case. Now click on OK. Now I want to execute our package. So now we can see in unmatched output we are not getting any row. So all rows moved to our match output. Now I want to change case mode to no case. So stop this package double click on this lookup transformation and then no case. Now click on OK. Now I want to execute our package. So here we can see so we are getting the Eric's record in our match output. So we can say that in case of full case it treats as a case as too else case in sense too. Now we can stop this package and go to our case seven. So this case seven is totally related to null values. If we are getting any null values in our source and in our lookup table we have also null value. So what would be the output for match output and no match output. So go to on SSMS. Now we can execute or select a statement for case seven. So as we can see null values are available in source and lookup table. So go to on SSGT. Now we need to change the table. So double click on this already be sourced. Here we are going to check for case seven. So we can select case seven source lookup table. Now click on OK. Then double click on this lookup transformation. I am going to change access mode to full case then go to on connections. And lookup reference table for case seven we need to select. Then click on OK. Now we need to execute our package. Once we execute our package then we can see in our match output we are getting all the values. In our no match output we are not going to get any value. We can stop this package then go to on lookup transformation. And I am going to change case seven to full case then click on OK. Now we need to execute our package. As we can see null value is moved to no match output. So now I am going to change case seven to no case seven. So double click on this lookup transformation. And here we need to change case seven to no case seven. Then click on OK. Now we need to execute our package. So as we can see null value is moved to no match output. So as we can say that if we are going to use full case then two null values are treated as the same. Once we are going to change case mode to parcel case or no case then two null values are treated as a different. Now we can stop this package. So I hope you have a nice time about the lookup scenarios. So 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.