Sentiment Analysis in SSIS using Azure AI – Machine Learning REST API

Introduction to Sentiment Analysis in SSIS

sentiment analysis in SSISIn this article, we will show how to do sentiment Analysis in SSIS of text using artificial intelligence. It is very important for a company to measure customer satisfaction. Today, it is possible to do sentiment and use artificial intelligence and detect if a customer liked or not our products based in on a comment?
Yes, it is. Now it is possible to analyze text and detect customer feelings. In this article, we will show how to detect the feelings of a comment, how to export the results in a Database and how to display some information based on the feelings.

In this article we will be using ZappySys SSIS PowerPack components and Microsoft Azure Service for Text Analytics (i.e. Cognitive Services) to make things work:


  • In order to start, we will use SSDT with SQL Server Integration Services(SSIS). SSIS is a Microsoft Powerful tool to import, export data and automate tasks.
  • Secondly, ZappySys SSIS PowerPack, which is a powerful tool to make REST API calls and other tools that we will use to get data and analyze data and export the data.
  • Finally, an Azure Account will be required to access the Text Analytics Services.

Getting started

In order to start with the Sentiment Analysis in SSIS, we will show first how to analyze text and detect the customer feelings. If it is a positive feeling, the value will be 1 or close to 1. If the feeling is negative, the value will be 0 or close to 0. Let’s start with a simple example of 2 real customer reviews:

First review:

Second review

In order to detect the feelings, we will use the Microsoft Cognitive Services. Microsoft included in Azure pretty nice services to Analyze text, translate text, detect sentiments, language understanding, and several other services. In this example, we will use the sentiment detector service.

Create a service for Sentiment Analysis in SSIS using Azure

  1. First, log in to the Azure Portal.
  2. Secondly, in the Portal, press the + green icon and select AI+ Machine Learning and select Text Analysis:
    Also, enter a name, a location and select a resource group and a pricing Tier.
    Azure text analytics services

    Add text Analytics

  3. Finally, check the endpoints and the keys. We will use that information to connect with REST API in SSIS.
    Azure Endpoint for Text Analytics

    Endpoint in Azure

Using REST API for Sentiment Analysis in SSIS

  1. First, we want to detect the feelings. To do it, we will use the REST API task included with the ZappySys SSIS PowerPack.
    Tool to invoke Azure Text Analytics

    SSIS Rest API Task

  2. Secondly, we will specify the URL and in the Raw Edit we will add the key to enter. The URL is the Endpoint that we had in the Azure Portal. It should be something like this:
  3. Also, enter the key that can be obtained in the Azure Portal. It will be something like this in the HTTP Header:
  4. In addition, in the Body Request, we will create something like the following:
  5. In addition, the JSON text includes the ID (1 and 2), which is just an identifier. The language is to specify the language of the text. Currently it supports English (en), Spanish (es), Portuguese from Portugal (pt-PT) and other languages. For a complete list, you can check this link:
  6. The REST API tasks should look like this:
    REST API connection to Azure Text Analytics

    SSIS REST API connection to Azure Cognitive services

  7. Also, when we press the Test Request/Response button, we can see the score of the comments:
    Feeling scores

    Score detecting feelings

  8. Finally, the scores are 0.84 and 0.789. They are closer to 1, so they are 2 positive comments. So, we could read comments and detect if the feelings are positive or negative. Also, it is possible to store the results in a file or in a variable:
    Save JSON results in SSIS variables

    SSIS save JSON result to a variable

How to detect Sentiment Analysis in SSIS using a database

In the previous example, we show how to detect the sentiments of text. The text must be in JSON format, so it is necessary to convert the data from JSON. In this new example, we will show how to convert comments from an SQL Server database to JSON and then we will learn how to export the scores of the sentiments to SQL Server and count how many users are happy and unhappy. The package will have the following tasks:

Artificial intelligence package in SSIS

SSIS package to count positive and negative sentiments using AI

We will first show how to export reviews stored in an SQL Server database to a JSON format necessary to be read by REST API.

Export SQL Server data to JSON format

  1. First of all, we will use a table named comments. The code to create the table with data is the following:
    The table is this one:
    SQL table with some reviews

    Sample table to detect feelings

  2. Secondly, we want to convert this SQL table to JSON like this to send the data to our Azure REST API text analytics service:
  3. Also, will use the Export JSON Task include in the ZappySys SSIS PowerPack.
    First, in Source, enter your server name, connection, and SQL Server Database and check the Use Custom Layout option:
    Task in SSIS to export from SQL Server to JSON

    SSIS export to JSON task

  4. Check the Single Dataset Array option and on Dataset, right click and select Add Dataset:
    SSIS task to export to JSON

    Add JSON data set

  5. Also, write a DataSet name, select the SQL Server database connection and select the table comment that contains the comments to export to JSON. You can export data from Tables, Views, queries and stored procedures with parameters using our Export JSON Task:
    Task in SSIS to get data from SQL Server to JSON

    SSIS get tables views to convert to JSON

  6. In addition, right-click Mapping and select Add Document Array:
    Next, add an Element name and press OK:
    Add JSON array document in SSIS

    SSIS add array document

  7. Next, right-click documents and select Add Elements:
    JSON element in SSIS

    Add JSON element in SSIS

  8. Also, add the comments and the id:
    Elements in JSON

    JSON elements added in SSIS

  9. The format displayed will be like this:
    SSIS Output in JSON format

    SSIS SQL Server data in JSON Format

  10. Another thing that you have to do is go to the Target tab, the target location will be a variable and we will store the JSON in a variable named jsonvar:
    SSIS Save JSON into variable

    Save JSON into variable

How to get the feelings using REST API

In the previous section, we exported data from SQL Server to a JSON format and stored the data in an SSIS variable named jsonvar. In this new section, we will call the API to get Analyze the text and detect if the comments are positive or negative.

SSIS detect feelings with REST API

REST API detect feelings SSIS

  1. First, we will use the REST API to invoke the Azure Analytics Text services. We will use the Web URL of the Create a service to Analyze text using Azure section at the beginning of the article and in the HTTP Headers, go Raw Edit and specify the Ocp-Apim-Subscription key. You can also see this information in the Analyze test using Azure section.
  2. Secondly, in the body (Request Data), we will use the variable with JSON results generated by the Export to JSON task in the previous section:
    Text Analysis using REST API in SSIS

    Header information REST API

  3. Finally, you will store the results in a file named sentiment.json using the Response Settings tab:
    SSIS JSON results into a file

    Save JSON results into a file in SSIS

Export JSON results to SQL Server

  1. We have a file with the Sentiments in a file names sentiments.json and we want to export the JSON file to SQL Server to count the values and do some queries. To do that, we will use the Data Flow task:
    SSIS to export text anallytics results to SQL Server

    Export from JSON to SQL in SSIS

  2. In the Data Flow task, use the JSON Source and join to the OLE DB Destination. The JSON Source will read our JSON file and the OLE DB Destination will connect to SQL Server:
    JSON Source to export data to SQL Server

    The JSON Source in SSIS

  3. Also, in JSON Source task, enter the path of the JSON file. In Filter write the following filter:
    SSIS JSON filters

    Add filters to JSON data

  4. Also, we will create the following table named feeling to store the JSON results to SQL Server and use the OLEDB Destination to store JSON values into the SQL table. In this example, the scores of the feelings:
    Table to store score of text analytics

    SQL Server SSIS Oledb

Count positive and negative feelings in SSIS

We stored the feelings in an SQL Server table. Now, we need to count them and then publish the values into variables.

SQL Query with single result

Store T-SQL results into

  1. In order to start, we will create two SSIS variables. One named positive and another negative of type int16. We will store the positive feelings in one variable and negative feelings in another.
  2. Secondly, we will use the Execute SQL Task. In connection, you must provide your SQL Server name, database, credentials. Go to Result Set and select single row.
  3. In SQLStatement, write the following query to count the positive feelings:
    Save SQL query result into an SSIS variable

    Save T-SQL count result in a variable

  4. Also, in result set specify 0 in Result Name and select the Positive variable.
    SSIS result set into a variable

    Handle SSIS Results Set

  5. In addition, to count the negative feeling we will use the same Execute SQL Task with the following query and store the results in the negative SSIS variable:
    SSIS store SQL results into variable

    Store number of negative sentiments into SSIS variable

  6. Finally, in Result Set, Assign the values to the negative variable:

Show positive and negative feelings using an SSIS variable

Finally, we will show the number of positive and negative feelings using the ZS Logging Task. This task allows storing variable values in a File, the Log file a messagebox. The article will show the value in the Output log.

  1. In order to start, in the ZS Logging task use the ExecutionLog Log mode. We will send the following text to show the variables in the output:


    Show number of negative comments

    SSIS variables in output

  2. Finally, if you run the package in the Visual Studio output, you will be able to see the that the positive feelings are 3 and negative feelings 1:
    SSIS variables values displayed

    Show variables in output

Conclusion about Sentiment Analysis in SSIS

In this article, we learned how to use text analytics using Microsoft Azure Services. Specifically, we use a sentiment detector to detect if sentiments are positive or negative using Artificial Intelligence.
Also, to detect that, we need to convert the text to JSON format. We learn how to convert data from an SQL Server database to JSON using our SSIS Export to JSON task. We also used our REST API to invoke the Microsoft Text Analytic services in Azure to detect the sentiments of our data.
Finally, we use the JSON Source to export the information to SQL Server and count the positive and negative sentiments.
To conclude, If you want to try yourself, you can download the ZappySys SSIS PowerPack here.

References to Sentiment Analysis in SSIS

Finally, for more information about Text Analytics, JSON conversion, refer to these links:

Posted in REST API, REST API Integration, SSIS JSON Source (File/REST), SSIS PowerPack and tagged , , , , , , , .