 Hello friends, welcome to SSUnitech. So, see this side. And this is continuation of SSIS tutorial. So, today we gonna discuss about fuzzy lookup transformation. So, what is fuzzy lookup transformation and when we can use it. So, before going forward, I would strongly recommend to watch lookup transformation if you haven't watched. So, let's start with fuzzy lookup. So, it is used to replace the wrongly typed words with the correct words. Basically, it uses fuzzy matching to find one or more close matches in the reference table and replace our source data by reference table data. And it uses only OLEDB connection manager to establish connection with the reference table. In our reference table, we cannot use accept OLEDB source. Next is, it allow us to use only dt underscore w str and dt underscore str data types column for fuzzy matching. So, it would take only wire care and n wire care data types. Next is, this transformation creates temporary table and indexes in SQL server database at runtime. So, as we can see, this is our scenario. We have a flat file source that contains the information of employees. And while entering the titles, which is our designation, we may enter the incorrect titles. When we try to use lookup transformation, then we will not get output due to incorrect titles because lookup transformation will use for exact match. So, this transformation will help us to find the nearest match and get the output. So, here as we can see we have flat file source, then we need to use fuzzy lookup transformation to correct source data by using reference table data. And then we want to load that data in our OLEDB destination. So, let's have a look of flat file source. Go to one folder, then double click on this fuzzy example. So, as we can see, we have last name, first name and titles. So, in title, as we can see, we are getting cussed service REP dot. In our very next row, as we can see customer service representative. So, this is our correct title. Next is Tech Lead. This is our correct title. Then senior manager, assistant manager, again senior manager. Then we can see for Rajesh Kumar, we are getting source then MGR dot. So, our correct title would be store manager. So, by using fuzzy lookup transformation, we will replace these incorrect titles by correct titles. So, let's have a look of our reference table. So, go to on SSMS. Then we can see we have our occupation table. So, I gonna execute this selected statement. Then we can see these are our correct titles. As we can see, when we gonna use lookup transformation, then occupation description as we can see customer service representative. So, our first row will not be matched. So, lookup transformation will be failed. Then we have to use fuzzy lookup transformation to replace these incorrect titles by correct titles. And here we can see fuzzy lookup output table. Obviously, we don't have any data. As we can see, we have ID, first name, last name, and titles. And ID is our identity column. So, as we can see, ID, identity, seed one and increment by one. So, go to on SSGT to implement the package. So, here I gonna add one more package. So, right click on this SSIS package. Then new SSIS package. Then we need to rename this package with fuzzy lookup. So, this is our fuzzy lookup. Then we need to use data flow task inside the control flow panel. So, we can drag and drop data flow task. Then double click on this data flow task. So, inside data flow panel, we need to configure our source, transformation and destination. As we have discussed, our source is flat file. So, we need to drag and drop flat file source. Then double click on this flat file source. So, here it would ask for flat file connection manager. So, I gonna click on new. Then click on browse. So, this is our file. We can select and click on open. So, column names in the first data row, that is okay. Go to on columns. So, as we can see last name, first name and titles. So, title would be more than 50 characters. So, go to on advance. Then we can increase the size of title. So, I gonna increase by 255. Then click on okay. Here we can select this checkbox. Then go to on columns. We want all the column. Click on okay. So, we have done our source. Now, we need to configure our transformation. So, our transformation is fuzzy lookup. So, we can drag and drop this fuzzy lookup transformation. Then we need to connect with flat file source. Now, double click on this fuzzy lookup. Then as we can see, we have three tabs. In our first tab, we have reference table. So, here we need to select our OLEDB connection manager. So, click on new. As we have already made the connection. So, I gonna use that one. Click on okay. So, here we can see generate new index. Then a reference table name. Store new index. New index name. Maintain stored index. Use existing index and name of an existing index. So, here these are the lookup properties. First is generate new index. So, it would generate a new index on the reference table. That would help to fuzzy lookup. Next is store new index. If we want to create a new index on reference data, then we can select this option. Next is new index name. If we select store new index option, then here we need to specify index name. Next is maintain store index. If we want SQL Server will maintain the newly created index, then we can select this option. Next is use existing index. If we want to use already created index, then we can select this option. And our last option is name of an existing index. When we select our use existing index option, then we need to select the existing index by using drop down. So, go to on SSDT. Then I wanna generate a new index. Then we need to select our reference table name. As we have discussed, occupation table is our reference table. Now go to on columns. So, in column as we can see, these are our input columns and these are available lookup columns. So, we wanna match with title, then occupation. And here we also want to get correct occupation. So, we will get occupation description from available lookup columns. Then go to on advance. And this is very important. So, here as we can see, we have similarity threshold value. So, similarity threshold ranges between 0 to 1, where 1 is for exact match. The fuzzy lookup editor provides a slider to adjust similarity between 0 to 1. If it's close to 1, then source should match more accurately. And also it would generate two extra columns with our existing column. First is similarity. Second is confidence. So, in our similarity, its score between 0 to 1, it will show us how much accurate source is matched with the fuzzy lookup data. For example, if it's 0.5, that means source is 50% matched with the reference data. Second is our confidence. So, it shows us how much confident fuzzy lookup is about the best match in the fuzzy lookup table. For example, 0.5.0 means fuzzy lookup is 50% confident. So, go to on ssdt. So, this is our similarity threshold value. So, I gonna set this value 0.7 and here we can see we have token delimiters. So, I gonna use space and dot as our token delimiters. Now, I am going to additional delimiters with dot. So, now click on OK. So, we have done our fuzzy lookup. Now, we need to load that data in our destination. So, before loading data, I would like to check the data. So, go to on common. Then we can drag and drop multicast and connect with our fuzzy lookup. Now, we can enable our data then we can save our package and execute. So, here as we can see occupation description has funeral values. So, similarity threshold value is not correct. So, it is our heated dry method. So, we can close this and stop this package. Then we need to change our similarity threshold value by 0.5. Then click on OK. Then we can save it and execute our package. So, this time as we can see we are getting occupation descriptions, but there is no any null values are available. So, this is our perfect value for similarity threshold. So, here as we can see we are getting extra columns. First is similarity, second is confident. So, as we have discussed similarity and confidence. So, similarity and similarity titles both are same. As we can see these are getting the same values. Now, we can close this data and stop our package. Now, we need to configure our destination. So, delete this multicast and we need to use already be destination. So, we can drag and drop and connect with fuzzy lookup transformation. Then double click on this already be destination. So, here we need to select the table. So, our table is fuzzy lookup output table, good one mapping. So, as we can see we are getting available input columns and available destination columns. So, last name mapped with last name that is fine. Then first name mapped with first name that is also fine and there is no need to provide any value for ID because it's identity column. So, it would be generated automatically. But once we talk about titles then our available input columns we have titles and occupation description. So, we have two columns and by default titles mapped with titles because our available input columns and available destination columns both are same. But in our titles we are getting incorrect titles. In our occupation description we are getting the correct occupation. So, we can map this with the title in our destination. Now, we can click on OK. So, now we can save this package and go to on SSMS and we can execute our destination table. So, by default we don't have any data. Now, we need to execute our package. So, click on start. So, as we can see 21 rows should be inserted in our destination with correct titles. Can you stop this package? Go to on SSMS. Now, we can execute this selected statement one more time. As we can see we are getting customer service representative, customer service representative, tech lead, senior manager, assistant manager. So, we are getting correct data. So, I hope you have understand how we can use fuzzy lookup transformation and this is very important. 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.