"Bi thing" data stream conversion--word Lookup transformation

Source: "Bi thing" data stream conversion--word Lookup transformation

The term Lookup transformation matches the words extracted from the text of the converted input column to the words in the reference table, and then calculates the number of occurrences of the word in the lookup table in the input dataset and writes the count to the column in the transformation output with the word in the reference table. This conversion is useful for creating a custom word list that is based on input text and has frequency statistics.

This chapter functions: Take out the data of a field in a table and remove the keywords from the other table to determine the number of occurrences of the keyword in the source record.
To create the simulation data:

--the table used for lookupsCREATE TABLE [Custfeedback]    (      [Srlno] INT IDENTITY(1,1) ,      [UserID] INT ,      [Feedback] NVARCHAR( -)    )  GO  --keyword listCREATE TABLE [Searchkey]    (      [Srlno] INT IDENTITY(1,1) ,      [Keyword] NVARCHAR( -)    )  GO    INSERT   into [Custfeedback]        ( [UserID],[Feedback] )VALUES( -,'Heard Touch screen' ),        ( 101,'Smooth Slicing' ),        ( 102,'Getting problem in installing new application' ),        ( 103,'application get hangs Duringcall' ),        ( 104,'Touch Screen not working properly' ),        (  the,'No Long Battery backup' ),        ( 106,'Phone get hang and playing games' ),        ( 107,'Screen size is too small' ),        ( 108,'qualty of screen guard are bad' ),        ( 109,'bilt-in Memory is too small' ),        (  the,'Rescreen' ),        ( 111,' Screen and screen' )  GO    INSERT   into [Searchkey]        ( [Keyword] )VALUES(' Screen' ),        ( 'Touch Screen not working properly' )  GO  SELECT  * fromCustfeedbackSELECT  * fromSearchkeySELECT  *

Create a Data Flow task and double-click Edit. First, create a data flow source "OLE DB source"
Edit the OLE DB source to connect to the table you are looking for.

Add the Data Transformation component. The term Lookup transformation can only use columns that have a data type of DT_WSTR or Dt_ntext. If a column contains text but does not have one of these two data types, the Data transformation can add a column of the data type DT_WSTR or Dt_ntext to the data flow and copy the column values to the new column. The output of the data transformation can then be used as input to the term Lookup transformation.
Edit the component. The column that will be used to find the Feedback transformation data type is Unicode text stream [Dt_ntext] and the output name is changed to Converted_feedback.

Add the term Lookup component to the data flow:

Editing components, you can only connect to the database in OEL DB mode. Connect to the database and reference the keyword table:

Click the option term lookup, set the lookup reference, and associate the input column Converted_feedback "Keyword" to the column that can be referenced.
Also tick the UserID and Converted_feedback as the output.

Advanced options can also set whether case-sensitive lookups. Keywords will ignore the first letter in uppercase.

Add, Target Component "OLE DB destination":
Edit the component, connect to any database, and create a new table:

Erms: Keywords found in input columns
Frequency: Number of occurrences of keywords per line
UserID: The userid column of table Custfeedback
Converted_feedback: The column being looked up

"Bi thing" data stream conversion--word Lookup transformation