 Hello, welcome to SSUnitech. So, see you this side. And this is continuation of SSIS tutorial. So, today we gonna discuss about term lookup transformation. So, what is term lookup transformation and when we can use it? So, before going forward, if you haven't watched last video of this video series, so I would strongly recommend to watch that video. So, let's start with term lookup. So, it is used to analyze the set of text records. Next is, it looks the set of predefined nouns and phrases in the input text column that should be our OLDB source and provide count of occurrence as an output on row by row basis. It will accept only unicode string that is dt underscore WSTR and unicode text stream that is dt underscore ntext. So, it will accept only dt underscore WSTR and dt underscore ntext as we have discussed in our last video as well. So, in output of term lookup, we will get two extra columns, terms and frequency. So, these two columns will be added in our output. So, let's have a look of scenario. So, this is our scenario as we can see, we have a source table that contains the information of emails and we have a reference table that contains the information of nouns and phrases. So, as we can see, we have a OLDB source, here we have information of emails and in our term lookup, we have a reference table that contains the information of nouns and phrases and we don't want to load data in destination, we just want to check the data, that's why I have used multicast. So, let's have a look of table, go to on SSMS, then we can see term lookup source table, then we can execute our selected statement as we can see we have the emails data. Now, go to on our reference table, let's execute the selected statement for reference table. So, as we can see, we are getting the courses, that is our nouns. So, Python, SQL, SSIS, SSIS and SSRS. Go to on SSGT to implement the package. So, here I want to add one more package. So, right click on this SSIS package, then new SSIS package. Then we need to rename this package. So, this is for term lookup, so we can rename with term lookup. So, then we have to use a Dataflow task. So, we can drag and drop this Dataflow task, then double click on this Dataflow task. So, here we need to configure our source. So, our source is equals to our table, that's why we have to use OLEDB source. So, now we need to double click on this OLEDB source, then we need to select OLEDB connection manager. So, I am going to click on new. So, I have already made the connection. So, click on OK. Here we need to select the table. So, our table is term lookup source table. So, this is our table. Now, go to on columns, we are getting ID and mail description. So, click on OK. So, we have done our source. Now, we need to configure our transformation. So, our transformation is term lookup transformation. So, we need to drag and drop this term lookup transformation, then we need to connect with source. Now, double click on this term lookup transformation. So, as we can see our refresh table. So, here we need to select our refresh table. So, our refresh table is term lookup reference table, then go to on term lookup tab. So, in this tab, we want to apply lookup with mail description from our input column and in our refresh table with courses. So, we can drag and drop. And in our output, we want all these columns. So, we can select ID and mail description as well. Go to on advanced tab. Here we can see we can select the case sensitive option, but I am not going to use this option. So, as we can see it is going to reflect and warning. Rows send to error output if the rows will be lost. So, double click on this term lookup transformation, then we need to click on configure error output. We can click on ignore failure. And here again we can select ignore failure. Then apply and click on OK. Then again OK. So, we have done our transformation. Now, we do not want to load data in any destination. So, we just want to check the data. So, we can use multicast. Then connect with term lookup transformation. Here we can enable data viewer. So, right click on this data pipeline, then enable data viewer. Now, we can execute our package. So, as we can see, this is ID and this is mail description. So, we are getting the data, but we are getting two additional columns as I have discussed term and frequency. So, we can stop this package. Next is how term lookup works. So, first is if referent table contains similar form of world, then it would be considered both similar and plural form of data in our source as singular. As we can see here, source contains student and students. So, we are getting two rows. And in our reference, we have only student. So, in our output, we will get students two time and it will reflect student and students. So, go to on SSMS. So, here in our case one, I want to insert student in our reference table. I want to insert student and students in our lookup source table. So, as we can see two rows affected. Now, go to on SSTT and execute our package. So, click on start. So, as we can see student and students. So, we are getting both recall. Now, we can stop this package. In our second, as we can see if reference table contain plural form of world, then this transformation considered singular and plural form separately at source. As we can see, source contains student and students. And in our reference, we have students. So, our output will student. Go to on SSMS. And this time I want to update this lookup reference table with students. So, one row affected. Now, go to on SSTT. Now, I need to execute this package one more time. So, as we can see, we are getting students. Students and students both are treated separately. So, we can stop this package. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.