The Term Lookup Transformation in SSIS is used to find terms with in the string or text. It uses the reference table (contains terms) to find the matched sentences from the source data and also counts the number of times a term is repeated in the source data row.
The SSIS Term Lookup transformation adds two extra columns to the transformation output called as Term and Frequency by default. Term contains a term from the lookup table and Frequency contains the number of times the term is repeated or occurred in the input data set.
TIP: In SSIS, Term Lookup Transformation only allows the columns with DT_WSTR and DT_NTEXT data type to perform term lookup. If your input column is different from these two then Please use the SSIS Data Conversion to convert other Data Types to DT_WSTR and DT_NTEXT data type.
For example, if your company has 100 products and it is allowing users to write reviews on your products. Imagine you are getting 2000 reviews per day then, how will you find which review belongs to which product? To resolve these type of issues SSIS introduced Term Lookup Transformation. Using this you can find which row has product 1 review and which row has product 90 review.
TIP: Please refer Term Extraction Transformation in SSIS article to understand the term extraction technique
Term Lookup Transformation Matching Techniques
Term Lookup Transformation uses following rules to perform Term lookup on the source Data:
- If the reference table contains singular form of the word then Term Lookup Transformation consider both the singular and plural forms of the word in the source data as singular form. For example, if the term lookup table contains Employee and we have Employee and Employees terms in source data then Term Lookup Transformation would count both of them as a match for the lookup term
- If the reference table contains plural form of the word then Term Lookup Transformation consider both the singular and plural forms of the word in the source data as separate Terms. For example, if the term lookup table contains Employees and we have Employee and Employees terms in source data then Term Lookup Transformation would count Employee separately and Employees separately.
- If we configured the Term Lookup Transformation to perform case-sensitive matches then employee is treated separately from EMPLOYEE. If we have Employee as a first word in the sentence then Term Lookup Transformation will count Employee and employee as same term.
Term Lookup Transformation in SSIS Example
In this example we are going to show you, How to configure Term Lookup Transformation in SSIS with sample data.
Here is our Lookup Source table
Here is our lookup table from the [SSIS Tutorials] Database.
STEP 1: Create new project in BIDS with Integration Service Project Template. Drag the Data flow task to Control flow region and rename it as per your requirements.
Double click on it and it will open the data flow tab.
STEP 2: Drag and drop OLE DB Source, Term Lookup Transformation and OLE DB destination from toolbox to data flow region
STEP 3: Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement or we can select the table from Drop down list.
STEP 4: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
STEP 5: Click ok and edit the Term Lookup Transformation
In the reference table tab, Configure the OLE DB connection manager to reference database (Here it is [SSIS Tutorials]) and then select the table you wish to add as reference table (Here it is Term Lookup Transformation table).
TIP: This transformation only supports OLE DB Connection Manager to connect with the reference table.
NOTE: This Transformation will accept only Unicode String [DT_WSTR] or Unicode text stream [DT_NTEXT] as its data type.
Step 6: Use the Term Lookup tab to map an input column to a lookup column in a reference table and to provide an alias for each output column.
Use the Advanced tab of the Transformation Editor Dialog box to specify whether the lookup should be case-sensitive or not.
From the below screenshot you can see, there is a warning symbol on the transformation and it is telling that error output is not connected. You can remove the warning symbol by configuring the error output. So double click on the Configure Error Output button will open new window to configure the error output.
The default configuration of this Transformation is to redirect error rows. You can get rid of this warning by connecting the error output or by changing the default behavior to Ignore Failure or Fail Component. Let me change to Ignore Failure
Click ok to finish configuring the Transformation.
STEP 7: Now we have to provide Server, database and table details of the destination. So double click on the OLE DB Destination and provide the required information
From the above screenshot you can observe that, We selected the [SSIS Tutorials] database as our destination database and [Term Lookup Transformation Output] table as destination table.
STEP 8: Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click ok to finish designing our SSIS Transformation package. Let us run the package
Let’s open the SQL Server Management Studio and check the results
From the above screenshot you may see, this transformation is displaying the Term and Frequency (number of times term is repeating) of the Term in a sentence.
Thank you for Visiting Our Blog