{"id":3791,"date":"2018-06-07T21:05:48","date_gmt":"2018-06-07T21:05:48","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=3791"},"modified":"2025-03-07T12:39:04","modified_gmt":"2025-03-07T12:39:04","slug":"sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/","title":{"rendered":"Sentiment Analysis in SSIS using Azure AI &#8211; Machine Learning REST API"},"content":{"rendered":"<h2>Introduction to Sentiment Analysis in SSIS<\/h2>\n<p><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-3851 alignleft\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png\" alt=\"sentiment analysis in SSIS\" width=\"93\" height=\"93\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png 185w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics-150x150.png 150w\" sizes=\"(max-width: 93px) 100vw, 93px\" \/><\/a>In this article, we will show how to do <strong>sentiment Analysis in SSIS<\/strong> 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 <strong>artificial intelligence<\/strong> and detect if a customer liked or not our products based in on a comment?<br \/>\nYes, 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.<\/p>\n<p>In this article we will be using <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/\" target=\"_blank\" rel=\"noopener\">ZappySys SSIS PowerPack<\/a>\u00a0components and <strong>Microsoft Azure Service for <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/cognitive-services\/text-analytics\/\" target=\"_blank\" rel=\"noopener\">Text Analytics<\/a><\/strong>\u00a0(i.e. <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/cognitive-services\/\" target=\"_blank\" rel=\"noopener\">Cognitive Services<\/a>) to make things work:<br \/>\n<div class=\"su-table su-table-alternate\">\n<table width=\"300\">\n<tbody>\n<tr style=\"line-height: 0px\">\n<td width=\"50px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-rest-api-web-service-task\/\" target=\"_blank\" rel=\"noopener\"><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3074\" src=\"https:\/\/zappysys.com\/images\/SSIS-PowerPack\/ssis-rest-api-web-service-task.png\" alt=\"SSIS REST API Web Service Task \" width=\"50\" height=\"50\" \/><\/a><\/td>\n<td style=\"vertical-align: middle\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-rest-api-web-service-task\/\" target=\"_blank\" rel=\"noopener\">SSIS REST API Web Service Task<br \/>\n<\/a><\/td>\n<\/tr>\n<tr style=\"line-height: 0px\">\n<td style=\"height: 58px\" width=\"50px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-file-source\/\" target=\"_blank\" rel=\"noopener\"><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3074\" src=\"https:\/\/zappysys.com\/images\/SSIS-PowerPack\/SSIS-Json-Source-Adapter.png\" alt=\"SSIS JSON Source (File, REST API, OData Connector)\" width=\"50\" height=\"50\" \/><\/a><\/td>\n<td style=\"vertical-align: middle;height: 58px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-file-source\/\" target=\"_blank\" rel=\"noopener\">SSIS JSON Source (File, REST API, OData Connector)<\/a><\/td>\n<\/tr>\n<tr style=\"line-height: 0px\">\n<td style=\"height: 58px\" width=\"50px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-file-destination-connector\/\" target=\"_blank\" rel=\"noopener\"><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3074\" src=\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/json-file-destination\/ssis-json-file-destination.png\" alt=\"SSIS JSON File Destination (Create JSON File)\" width=\"50\" height=\"50\" \/><\/a><\/td>\n<td style=\"vertical-align: middle;height: 58px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-file-destination-connector\/\" target=\"_blank\" rel=\"noopener\">SSIS JSON File Destination (Create JSON File)<br \/>\n<\/a><\/td>\n<\/tr>\n<tr style=\"line-height: 0px\">\n<td style=\"height: 58px\" width=\"50px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-export-json-file-task\/\" target=\"_blank\" rel=\"noopener\"><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3074\" src=\"https:\/\/zappysys.com\/images\/SSIS-PowerPack\/ssis-export-json-file-task.png\" alt=\"SSIS JSON File Destination (Create JSON File)\" width=\"50\" height=\"50\" \/><\/a><\/td>\n<td style=\"vertical-align: middle;height: 58px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-export-json-file-task\/\" target=\"_blank\" rel=\"noopener\">SSIS Export JSON File Task<br \/>\n<\/a><\/td>\n<\/tr>\n<tr style=\"line-height: 0px\">\n<td style=\"height: 58px\" width=\"50px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-logging-task-free\/\" target=\"_blank\" rel=\"noopener\"><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3074\" src=\"https:\/\/zappysys.com\/images\/SSIS-PowerPack\/ssis-logging-task.png\" alt=\"SSIS JSON File Destination (Create JSON File)\" width=\"50\" height=\"50\" \/><\/a><\/td>\n<td style=\"vertical-align: middle;height: 58px\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-logging-task-free\/\" target=\"_blank\" rel=\"noopener\">SSIS Logging Task (FREE)<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h2>Requirements<\/h2>\n<ul>\n<li>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.<\/li>\n<li><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/download\/\">Secondly, ZappySys SSIS PowerPack<\/a>, 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.<\/li>\n<li>Finally, an <strong>Azure<\/strong> Account will be required to access the <strong>Text Analytics Services<\/strong>.<\/li>\n<\/ul>\n<h2>Getting started<\/h2>\n<p>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\u2019s start with a simple example of 2 real customer reviews:<\/p>\n<p>First review:<\/p>\n<pre class=\"lang:default highlight:0 decode:true\">I looked everywhere for a product that could easily integrate to a specific web portal backend API via \r\nJSON, and after many attempts, the ZappySys product was the only solution that could give me all the \r\noptions I needed in a quick &amp; clean interface within SSIS. Coupled with some great tech support and \r\ncustomer support, I would definitely recommend ZappySys SSIS PowerPack...<\/pre>\n<p>Second review<\/p>\n<pre class=\"lang:default highlight:0 decode:true\">The responsiveness of your support staff during the evaluation period really has been excellent. \r\nI\u2019ve contacted them a few times with queries specific to the scenario I am working with and they have \r\nanswered straight away via chat and provide exactly the detailed technical input required to progress...<\/pre>\n<p>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.<\/p>\n<h3>Create a service for Sentiment Analysis in SSIS using Azure<\/h3>\n<ol>\n<li>First, log in to the <a href=\"https:\/\/portal.azure.com\">Azure Portal<\/a>.\n<div class=\"mceTemp\"><\/div>\n<\/li>\n<li>Secondly, in the Portal, press the + green icon and select <strong>AI+ Machine Learning<\/strong> and select <strong>Text Analysis<\/strong>:<br \/>\nAlso, enter a name, a location and select a resource group and a pricing Tier.<\/p>\n<div id=\"attachment_3804\" style=\"width: 881px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-Analytics-API-Service.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3804\" class=\"size-full wp-image-3804\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-Analytics-API-Service.png\" alt=\"Azure text analytics services\" width=\"871\" height=\"429\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-Analytics-API-Service.png 871w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-Analytics-API-Service-300x148.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-Analytics-API-Service-768x378.png 768w\" sizes=\"(max-width: 871px) 100vw, 871px\" \/><\/a><p id=\"caption-attachment-3804\" class=\"wp-caption-text\">Add text Analytics<\/p><\/div><\/li>\n<li>Finally, check the endpoints and the keys. We will use that information to connect with REST API in SSIS.\n<div id=\"attachment_3805\" style=\"width: 1240px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-analytics-endpoint.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3805\" class=\"size-full wp-image-3805\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-analytics-endpoint.png\" alt=\"Azure Endpoint for Text Analytics\" width=\"1230\" height=\"358\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-analytics-endpoint.png 1230w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-analytics-endpoint-300x87.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-analytics-endpoint-768x224.png 768w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Azure-text-analytics-endpoint-1024x298.png 1024w\" sizes=\"(max-width: 1230px) 100vw, 1230px\" \/><\/a><p id=\"caption-attachment-3805\" class=\"wp-caption-text\">Endpoint in Azure<\/p><\/div><\/li>\n<\/ol>\n<h3>Using REST API for Sentiment Analysis in SSIS<\/h3>\n<ol>\n<li>First, we want to detect the feelings. To do it, we will use the REST API task included with the ZappySys SSIS PowerPack.\n<div id=\"attachment_3806\" style=\"width: 329px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-rest-api-azure.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3806\" class=\"size-full wp-image-3806\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-rest-api-azure.png\" alt=\"Tool to invoke Azure Text Analytics\" width=\"319\" height=\"219\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-rest-api-azure.png 319w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-rest-api-azure-300x206.png 300w\" sizes=\"(max-width: 319px) 100vw, 319px\" \/><\/a><p id=\"caption-attachment-3806\" class=\"wp-caption-text\">SSIS Rest API Task<\/p><\/div><\/li>\n<li>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:\n<pre class=\"lang:default highlight:0 decode:true \">https:\/\/eastus2.api.cognitive.microsoft.com\/text\/analytics\/v2.0\/sentiment<\/pre>\n<\/li>\n<li>Also, enter the key that can be obtained in the Azure Portal. It will be something like this in the HTTP Header:\n<pre class=\"lang:default highlight:0 decode:true\">Ocp-Apim-Subscription-Key: 0e99402669b54beaa2af705d6c<\/pre>\n<\/li>\n<li>In addition, in the Body Request, we will create something like the following:\n<pre class=\"lang:js highlight:0 decode:true \">{\r\n  \"documents\": [\r\n    {\r\n      \"language\": \"en\",\r\n      \"id\": \"1\",\r\n      \"text\": \"I looked everywhere for a product that could easily integrate to a specific web portal backend api via JSON, and after many attempts, the ZappySys product was the only solution that could give me all the options I needed in a quick &amp; clean interface within SSIS.\"\r\n    },\r\n    {\r\n      \"language\": \" en \",\r\n      \"id\": \" 2 \",\r\n      \"text\": \"The responsiveness of your support staff during the evaluation period really has been excellent.I\u2019ve contacted them a few times with queries specific to the scenario I am working with and they have answered straight away via chat and provide exactly the detailed technical input required to progress.\"\r\n    }\r\n  ]\r\n}<\/pre>\n<\/li>\n<li>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:\n<pre class=\"lang:default decode:true\">https:\/\/docs.microsoft.com\/en-us\/azure\/cognitive-services\/text-analytics\/text-analytics-supported-languages<\/pre>\n<\/li>\n<li>The REST API tasks should look like this:\n<div id=\"attachment_3807\" style=\"width: 810px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-rest-api-azure-text-analytics.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3807\" class=\"size-full wp-image-3807\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-rest-api-azure-text-analytics.png\" alt=\"REST API connection to Azure Text Analytics\" width=\"800\" height=\"654\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-rest-api-azure-text-analytics.png 800w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-rest-api-azure-text-analytics-300x245.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-rest-api-azure-text-analytics-768x628.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/a><p id=\"caption-attachment-3807\" class=\"wp-caption-text\">SSIS REST API connection to Azure Cognitive services<\/p><\/div><\/li>\n<li>Also, when we press the Test Request\/Response button, we can see the score of the comments:\n<div id=\"attachment_3808\" style=\"width: 622px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-detect-feeling-text-analysis-positive-negative-feeling.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3808\" class=\"size-full wp-image-3808\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-detect-feeling-text-analysis-positive-negative-feeling.png\" alt=\"Feeling scores\" width=\"612\" height=\"579\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-detect-feeling-text-analysis-positive-negative-feeling.png 612w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-detect-feeling-text-analysis-positive-negative-feeling-300x284.png 300w\" sizes=\"(max-width: 612px) 100vw, 612px\" \/><\/a><p id=\"caption-attachment-3808\" class=\"wp-caption-text\">Score detecting feelings<\/p><\/div><\/li>\n<li>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:\n<div id=\"attachment_3809\" style=\"width: 810px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-API-results-to-variable-file.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3809\" class=\"size-full wp-image-3809\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-API-results-to-variable-file.png\" alt=\"Save JSON results in SSIS variables\" width=\"800\" height=\"654\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-API-results-to-variable-file.png 800w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-API-results-to-variable-file-300x245.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-API-results-to-variable-file-768x628.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/a><p id=\"caption-attachment-3809\" class=\"wp-caption-text\">SSIS save JSON result to a variable<\/p><\/div><\/li>\n<\/ol>\n<h3>How to detect Sentiment Analysis in SSIS using a database<\/h3>\n<p>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:<\/p>\n<div id=\"attachment_3810\" style=\"width: 370px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-to-json-rest-api.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3810\" class=\"size-full wp-image-3810\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-to-json-rest-api.png\" alt=\"Artificial intelligence package in SSIS\" width=\"360\" height=\"476\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-to-json-rest-api.png 360w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-to-json-rest-api-227x300.png 227w\" sizes=\"(max-width: 360px) 100vw, 360px\" \/><\/a><p id=\"caption-attachment-3810\" class=\"wp-caption-text\">SSIS package to count positive and negative sentiments using AI<\/p><\/div>\n<p>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.<\/p>\n<h3>Export SQL Server data to JSON format<\/h3>\n<ol>\n<li>First of all, we will use a table named comments. The code to create the table with data is the following:\n<pre class=\"lang:tsql decode:true\">CREATE TABLE [dbo].[comment](\r\n[comments] [nchar](2000) NULL,\r\n[id] [smallint] IDENTITY(1,1) NOT NULL,\r\nCONSTRAINT [PK_comment] PRIMARY KEY CLUSTERED\r\n(\r\n[id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO\r\nSET IDENTITY_INSERT [dbo].[comment] ON\r\n\r\nINSERT [dbo].[comment] ([comments], [id]) VALUES (N'Great product. I love it. ', 1)\r\nINSERT [dbo].[comment] ([comments], [id]) VALUES (N'It is not bad. I will use it. ', 2)\r\nINSERT [dbo].[comment] ([comments], [id]) VALUES (N'The product is wonderful ', 3)\r\nINSERT [dbo].[comment] ([comments], [id]) VALUES (N'I hate this product. I do not know why is it online ', 4)\r\nSET IDENTITY_INSERT [dbo].[comment] OFF<\/pre>\n<p>The table is this one:<\/p>\n<div id=\"attachment_3812\" style=\"width: 323px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-review-opinions-table.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3812\" class=\"size-full wp-image-3812\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-review-opinions-table.png\" alt=\"SQL table with some reviews\" width=\"313\" height=\"115\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-review-opinions-table.png 313w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-review-opinions-table-300x110.png 300w\" sizes=\"(max-width: 313px) 100vw, 313px\" \/><\/a><p id=\"caption-attachment-3812\" class=\"wp-caption-text\">Sample table to detect feelings<\/p><\/div><\/li>\n<li>Secondly, we want to convert this SQL table to JSON like this to send the data to our Azure REST API text analytics service:\n<pre class=\"lang:js decode:true\">{\r\n\"documents\": [\r\n{\r\n\"id\": 1,\r\n\"text\": \"Great product. I love it. \"\r\n},\r\n{\r\n\"id\": 2,\r\n\"text\": \"It is not bad. I will use it. \"\r\n},\r\n{\r\n\"id\": 3,\r\n\"text\": \"The product is wonderful \"\r\n},\r\n{\r\n\"id\": 4,\r\n\"text\": \"I hate this product. I do not know why is it online \"\r\n}\r\n]\r\n}<\/pre>\n<\/li>\n<li>Also, will use the Export JSON Task include in the ZappySys SSIS PowerPack.<br \/>\nFirst, in Source, enter your server name, connection, and SQL Server Database and check the Use Custom Layout option:<\/p>\n<div id=\"attachment_3814\" style=\"width: 833px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-custom-json-layout.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3814\" class=\"size-full wp-image-3814\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-custom-json-layout.png\" alt=\"Task in SSIS to export from SQL Server to JSON\" width=\"823\" height=\"612\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-custom-json-layout.png 823w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-custom-json-layout-300x223.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-custom-json-layout-768x571.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/a><p id=\"caption-attachment-3814\" class=\"wp-caption-text\">SSIS export to JSON task<\/p><\/div><\/li>\n<li>Check the Single Dataset Array option and on Dataset, right click and select Add Dataset:\n<div id=\"attachment_3815\" style=\"width: 828px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-json-task.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3815\" class=\"size-full wp-image-3815\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-json-task.png\" alt=\"SSIS task to export to JSON\" width=\"818\" height=\"315\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-json-task.png 818w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-json-task-300x116.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-json-task-768x296.png 768w\" sizes=\"(max-width: 818px) 100vw, 818px\" \/><\/a><p id=\"caption-attachment-3815\" class=\"wp-caption-text\">Add JSON data set<\/p><\/div><\/li>\n<li>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:\n<div id=\"attachment_3816\" style=\"width: 462px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-sql-to-json.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3816\" class=\"size-full wp-image-3816\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-sql-to-json.png\" alt=\"Task in SSIS to get data from SQL Server to JSON\" width=\"452\" height=\"438\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-sql-to-json.png 452w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-export-sql-to-json-300x291.png 300w\" sizes=\"(max-width: 452px) 100vw, 452px\" \/><\/a><p id=\"caption-attachment-3816\" class=\"wp-caption-text\">SSIS get tables views to convert to JSON<\/p><\/div><\/li>\n<li>In addition, right-click Mapping and select Add Document Array:<br \/>\nNext, add an Element name and press OK:<\/p>\n<div id=\"attachment_3818\" style=\"width: 612px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-add-json-document-array.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3818\" class=\"size-full wp-image-3818\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-add-json-document-array.png\" alt=\"Add JSON array document in SSIS\" width=\"602\" height=\"513\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-add-json-document-array.png 602w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/ssis-add-json-document-array-300x256.png 300w\" sizes=\"(max-width: 602px) 100vw, 602px\" \/><\/a><p id=\"caption-attachment-3818\" class=\"wp-caption-text\">SSIS add array document<\/p><\/div><\/li>\n<li>Next, right-click documents and select Add Elements:\n<div id=\"attachment_3819\" style=\"width: 833px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-add-element-JSON-array.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3819\" class=\"size-full wp-image-3819\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-add-element-JSON-array.png\" alt=\"JSON element in SSIS\" width=\"823\" height=\"612\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-add-element-JSON-array.png 823w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-add-element-JSON-array-300x223.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-add-element-JSON-array-768x571.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/a><p id=\"caption-attachment-3819\" class=\"wp-caption-text\">Add JSON element in SSIS<\/p><\/div><\/li>\n<li>Also, add the comments and the id:\n<div id=\"attachment_3820\" style=\"width: 405px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-elements-added.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3820\" class=\"size-full wp-image-3820\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-elements-added.png\" alt=\"Elements in JSON\" width=\"395\" height=\"438\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-elements-added.png 395w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-elements-added-271x300.png 271w\" sizes=\"(max-width: 395px) 100vw, 395px\" \/><\/a><p id=\"caption-attachment-3820\" class=\"wp-caption-text\">JSON elements added in SSIS<\/p><\/div><\/li>\n<li>The format displayed will be like this:\n<div id=\"attachment_3822\" style=\"width: 931px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-to-json.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3822\" class=\"size-full wp-image-3822\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-to-json.png\" alt=\"SSIS Output in JSON format\" width=\"921\" height=\"607\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-to-json.png 921w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-to-json-300x198.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/sql-server-to-json-768x506.png 768w\" sizes=\"(max-width: 921px) 100vw, 921px\" \/><\/a><p id=\"caption-attachment-3822\" class=\"wp-caption-text\">SSIS SQL Server data in JSON Format<\/p><\/div><\/li>\n<li>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:\n<div id=\"attachment_3823\" style=\"width: 833px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-JSON-result-to-variable.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3823\" class=\"size-full wp-image-3823\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-JSON-result-to-variable.png\" alt=\"SSIS Save JSON into variable\" width=\"823\" height=\"612\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-JSON-result-to-variable.png 823w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-JSON-result-to-variable-300x223.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-JSON-result-to-variable-768x571.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/a><p id=\"caption-attachment-3823\" class=\"wp-caption-text\">Save JSON into variable<\/p><\/div><\/li>\n<\/ol>\n<h3>How to get the feelings using REST API<\/h3>\n<p>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.<\/p>\n<div id=\"attachment_3824\" style=\"width: 384px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-REST-API-call-Azure-Text-Analytics.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3824\" class=\"size-full wp-image-3824\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-REST-API-call-Azure-Text-Analytics.png\" alt=\"SSIS detect feelings with REST API\" width=\"374\" height=\"469\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-REST-API-call-Azure-Text-Analytics.png 374w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-REST-API-call-Azure-Text-Analytics-239x300.png 239w\" sizes=\"(max-width: 374px) 100vw, 374px\" \/><\/a><p id=\"caption-attachment-3824\" class=\"wp-caption-text\">REST API detect feelings SSIS<\/p><\/div>\n<ol>\n<li>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.<\/li>\n<li>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:\n<div id=\"attachment_3825\" style=\"width: 810px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Detect-sentiment-analysys-in-SSIS.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3825\" class=\"size-full wp-image-3825\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Detect-sentiment-analysys-in-SSIS.png\" alt=\"Text Analysis using REST API in SSIS\" width=\"800\" height=\"654\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Detect-sentiment-analysys-in-SSIS.png 800w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Detect-sentiment-analysys-in-SSIS-300x245.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/Detect-sentiment-analysys-in-SSIS-768x628.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/a><p id=\"caption-attachment-3825\" class=\"wp-caption-text\">Header information REST API<\/p><\/div><\/li>\n<li>Finally, you will store the results in a file named sentiment.json using the Response Settings tab:\n<div id=\"attachment_3826\" style=\"width: 810px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-sentiment-detection-to-json-file.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3826\" class=\"size-full wp-image-3826\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-sentiment-detection-to-json-file.png\" alt=\"SSIS JSON results into a file\" width=\"800\" height=\"654\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-sentiment-detection-to-json-file.png 800w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-sentiment-detection-to-json-file-300x245.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-sentiment-detection-to-json-file-768x628.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/a><p id=\"caption-attachment-3826\" class=\"wp-caption-text\">Save JSON results into a file in SSIS<\/p><\/div><\/li>\n<\/ol>\n<h3>Export JSON results to SQL Server<\/h3>\n<ol>\n<li>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:\n<div id=\"attachment_3827\" style=\"width: 415px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-export-JSON-data-to-SQL-Server.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3827\" class=\"size-full wp-image-3827\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-export-JSON-data-to-SQL-Server.png\" alt=\"SSIS to export text anallytics results to SQL Server\" width=\"405\" height=\"235\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-export-JSON-data-to-SQL-Server.png 405w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-export-JSON-data-to-SQL-Server-300x174.png 300w\" sizes=\"(max-width: 405px) 100vw, 405px\" \/><\/a><p id=\"caption-attachment-3827\" class=\"wp-caption-text\">Export from JSON to SQL in SSIS<\/p><\/div><\/li>\n<li>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:\n<div id=\"attachment_3829\" style=\"width: 395px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-task.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3829\" class=\"size-full wp-image-3829\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-task.png\" alt=\"JSON Source to export data to SQL Server\" width=\"385\" height=\"230\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-task.png 385w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-task-300x179.png 300w\" sizes=\"(max-width: 385px) 100vw, 385px\" \/><\/a><p id=\"caption-attachment-3829\" class=\"wp-caption-text\">The JSON Source in SSIS<\/p><\/div><\/li>\n<li>Also, in JSON Source task, enter the path of the JSON file. In Filter write the following filter:\n<pre class=\"lang:default highlight:0 decode:true\">$.documents[*]\r\n<\/pre>\n<div id=\"attachment_3830\" style=\"width: 836px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-filter.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3830\" class=\"size-full wp-image-3830\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-filter.png\" alt=\"SSIS JSON filters\" width=\"826\" height=\"733\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-filter.png 826w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-filter-300x266.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-JSON-Source-filter-768x682.png 768w\" sizes=\"(max-width: 826px) 100vw, 826px\" \/><\/a><p id=\"caption-attachment-3830\" class=\"wp-caption-text\">Add filters to JSON data<\/p><\/div><\/li>\n<li>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:\n<pre class=\"lang:tsql decode:true\">CREATE TABLE [dbo].[Feeling]([score] \r\n[float] NULL,\r\n[id] [int] NULL ) \r\nON [PRIMARY] GO<\/pre>\n<div id=\"attachment_3831\" style=\"width: 831px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-SQL-Server-OLEDB-Destination.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3831\" class=\"size-full wp-image-3831\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-SQL-Server-OLEDB-Destination.png\" alt=\"Table to store score of text analytics\" width=\"821\" height=\"715\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-SQL-Server-OLEDB-Destination.png 821w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-SQL-Server-OLEDB-Destination-300x261.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-SQL-Server-OLEDB-Destination-768x669.png 768w\" sizes=\"(max-width: 821px) 100vw, 821px\" \/><\/a><p id=\"caption-attachment-3831\" class=\"wp-caption-text\">SQL Server SSIS Oledb<\/p><\/div><\/li>\n<\/ol>\n<h3>Count positive and negative feelings in SSIS<\/h3>\n<p>We stored the feelings in an SQL Server table. Now, we need to count them and then publish the values into variables.<\/p>\n<div id=\"attachment_3838\" style=\"width: 336px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-T-SQL-result-into-variable.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3838\" class=\"size-full wp-image-3838\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-T-SQL-result-into-variable.png\" alt=\"SQL Query with single result\" width=\"326\" height=\"471\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-T-SQL-result-into-variable.png 326w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-store-T-SQL-result-into-variable-208x300.png 208w\" sizes=\"(max-width: 326px) 100vw, 326px\" \/><\/a><p id=\"caption-attachment-3838\" class=\"wp-caption-text\">Store T-SQL results into<\/p><\/div>\n<ol>\n<li>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.<\/li>\n<li>Secondly, we will use the Execute SQL Task. In connection, you must provide your SQL Server name, database, credentials. Go to\u00a0Result Set\u00a0and select single row.<\/li>\n<li>In SQLStatement, write the following query to count the positive feelings:\n<pre class=\"lang:tsql decode:true \">select count(score) as positive \r\nfrom dbo.feeling \r\nwhere score&gt;0.5<\/pre>\n<div id=\"attachment_3839\" style=\"width: 747px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-select-count-into-variable.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3839\" class=\"size-full wp-image-3839\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-select-count-into-variable.png\" alt=\"Save SQL query result into an SSIS variable\" width=\"737\" height=\"631\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-select-count-into-variable.png 737w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-save-select-count-into-variable-300x257.png 300w\" sizes=\"(max-width: 737px) 100vw, 737px\" \/><\/a><p id=\"caption-attachment-3839\" class=\"wp-caption-text\">Save T-SQL count result in a variable<\/p><\/div><\/li>\n<li>Also, in result set specify 0 in Result Name and select the Positive variable.\n<div id=\"attachment_3841\" style=\"width: 747px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-result-set-variable.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3841\" class=\"size-full wp-image-3841\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-result-set-variable.png\" alt=\"SSIS result set into a variable\" width=\"737\" height=\"631\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-result-set-variable.png 737w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-result-set-variable-300x257.png 300w\" sizes=\"(max-width: 737px) 100vw, 737px\" \/><\/a><p id=\"caption-attachment-3841\" class=\"wp-caption-text\">Handle SSIS Results Set<\/p><\/div><\/li>\n<li>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:\n<pre class=\"lang:tsql decode:true \">select count(score) as negative \r\nfrom dbo.feeling \r\nwhere score&lt;0.5<\/pre>\n<div id=\"attachment_3843\" style=\"width: 747px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-negative-sentiment.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3843\" class=\"size-full wp-image-3843\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-negative-sentiment.png\" alt=\"SSIS store SQL results into variable\" width=\"737\" height=\"631\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-negative-sentiment.png 737w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-negative-sentiment-300x257.png 300w\" sizes=\"(max-width: 737px) 100vw, 737px\" \/><\/a><p id=\"caption-attachment-3843\" class=\"wp-caption-text\">Store number of negative sentiments into SSIS variable<\/p><\/div><\/li>\n<li>Finally, in Result Set, Assign the values to the negative variable:<\/li>\n<\/ol>\n<h3>Show positive and negative feelings using an SSIS variable<\/h3>\n<p>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.<\/p>\n<ol>\n<li>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:\n<pre class=\"lang:default highlight:0 decode:true \">Positive feelings: {{User::Positive}}.\r\nNegative feelings: {{User::Negative}}<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<div id=\"attachment_3847\" style=\"width: 635px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-variables-Output.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3847\" class=\"size-full wp-image-3847\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-variables-Output.png\" alt=\"Show number of negative comments\" width=\"625\" height=\"504\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-variables-Output.png 625w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-variables-Output-300x242.png 300w\" sizes=\"(max-width: 625px) 100vw, 625px\" \/><\/a><p id=\"caption-attachment-3847\" class=\"wp-caption-text\">SSIS variables in output<\/p><\/div><\/li>\n<li>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:\n<div id=\"attachment_3848\" style=\"width: 878px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-two-variables-in-output.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-3848\" class=\"size-full wp-image-3848\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-two-variables-in-output.png\" alt=\"SSIS variables values displayed\" width=\"868\" height=\"96\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-two-variables-in-output.png 868w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-two-variables-in-output-300x33.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/SSIS-show-two-variables-in-output-768x85.png 768w\" sizes=\"(max-width: 868px) 100vw, 868px\" \/><\/a><p id=\"caption-attachment-3848\" class=\"wp-caption-text\">Show variables in output<\/p><\/div><\/li>\n<\/ol>\n<h2>Conclusion about Sentiment Analysis in SSIS<\/h2>\n<p>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.<br \/>\nAlso, 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.<br \/>\nFinally, we use the JSON Source to export the information to SQL Server and count the positive and negative sentiments.<br \/>\nTo conclude, If you want to try yourself, you can download the <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/download\/\">ZappySys SSIS PowerPack here<\/a>.<\/p>\n<h2>References to Sentiment Analysis in SSIS<\/h2>\n<p>Finally, for more information about Text Analytics, JSON conversion, refer to these links:<\/p>\n<ul>\n<li><a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/cognitive-services\/text-analytics\/\">Microsoft Azure Text Analytics<\/a><\/li>\n<li><a href=\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/\">Call REST API using SSIS Web Service Task \/ JSON \/ XML Source<\/a><\/li>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/cognitive-services\/text-analytics\/how-tos\/text-analytics-how-to-sentiment-analysis\">How to detect sentiment in Text Analytics<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Introduction to Sentiment Analysis in SSIS In 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 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":3851,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[176,225,17,8],"tags":[331,183,333,6,332,3,334,12],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>Sentiment Analysis in SSIS using Azure AI - Machine Learning REST API | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"In this article, we will show how to do sentiment analysis in SSIS. We will invoke Azure Text Analytics services to detect sentiments and learn tools for it\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Sentiment Analysis in SSIS using Azure AI - Machine Learning REST API | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"In this article, we will show how to do sentiment analysis in SSIS. We will invoke Azure Text Analytics services to detect sentiments and learn tools for it\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2018-06-07T21:05:48+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-03-07T12:39:04+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"185\" \/>\r\n\t<meta property=\"og:image:height\" content=\"185\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys Team\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/\",\"url\":\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/\",\"name\":\"Sentiment Analysis in SSIS using Azure AI - Machine Learning REST API | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png\",\"datePublished\":\"2018-06-07T21:05:48+00:00\",\"dateModified\":\"2025-03-07T12:39:04+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\"},\"description\":\"In this article, we will show how to do sentiment analysis in SSIS. We will invoke Azure Text Analytics services to detect sentiments and learn tools for it\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png\",\"width\":185,\"height\":185,\"caption\":\"sentiment analysis in SSIS\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Sentiment Analysis in SSIS using Azure AI &#8211; Machine Learning REST API\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\",\"name\":\"ZappySys Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"caption\":\"ZappySys Team\"},\"sameAs\":[\"https:\/\/zappysys.com\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Sentiment Analysis in SSIS using Azure AI - Machine Learning REST API | ZappySys Blog","description":"In this article, we will show how to do sentiment analysis in SSIS. We will invoke Azure Text Analytics services to detect sentiments and learn tools for it","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/","og_locale":"en_US","og_type":"article","og_title":"Sentiment Analysis in SSIS using Azure AI - Machine Learning REST API | ZappySys Blog","og_description":"In this article, we will show how to do sentiment analysis in SSIS. We will invoke Azure Text Analytics services to detect sentiments and learn tools for it","og_url":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/","og_site_name":"ZappySys Blog","article_published_time":"2018-06-07T21:05:48+00:00","article_modified_time":"2025-03-07T12:39:04+00:00","og_image":[{"width":185,"height":185,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png","type":"image\/png"}],"author":"ZappySys Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys Team","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/","url":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/","name":"Sentiment Analysis in SSIS using Azure AI - Machine Learning REST API | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png","datePublished":"2018-06-07T21:05:48+00:00","dateModified":"2025-03-07T12:39:04+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e"},"description":"In this article, we will show how to do sentiment analysis in SSIS. We will invoke Azure Text Analytics services to detect sentiments and learn tools for it","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/cognitive-services-azure-text-analytics.png","width":185,"height":185,"caption":"sentiment analysis in SSIS"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/sentiment-analysis-in-ssis-azure-ai-machine-learning-rest-api\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Sentiment Analysis in SSIS using Azure AI &#8211; Machine Learning REST API"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e","name":"ZappySys Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","caption":"ZappySys Team"},"sameAs":["https:\/\/zappysys.com"],"url":"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3791"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=3791"}],"version-history":[{"count":27,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3791\/revisions"}],"predecessor-version":[{"id":11211,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3791\/revisions\/11211"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3851"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=3791"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=3791"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=3791"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}