Text Analysis with Term Lookup and Term Extraction transformations

There are a lot of transformations that help you to analyze numeric data. You can even use aggregate functions in T-SQL to fetch analytic information from numeric data; but what happens when you want to analyze user comments? Those can be freeform texts without any categorization.

Text Analysis is a complete topic in the data analysis category, which discusses fetching analytical information from text data; the text data can be completely freeform text data. Fortunately SSIS provides two useful and advanced transformations for Text Analysis called Term Extraction and Term Lookup.

In this recipe, we will take a look at how we can analyze and mine free text data. Assume that customers of a company share their experiences with the company products with freefrom text comments on the company's website.

Getting ready

Create a Product table in the PacktPub_SSISbook database with the following script:

CREATE TABLE [dbo].[Product](
  ...