{"id":8573,"date":"2019-12-20T10:02:31","date_gmt":"2019-12-20T10:02:31","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8573"},"modified":"2025-03-19T08:34:07","modified_gmt":"2025-03-19T08:34:07","slug":"read-data-from-elasticsearch-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-data-from-elasticsearch-using-ssis\/","title":{"rendered":"How to read data from ElasticSearch using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for ElasticSearch<\/a> which makes it much simpler to\u00a0Read\/Write ElasticSearch Data in SSIS<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid a learning curve with API then use a newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0preconfigured ready-to-use API connectors that you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, and Informatica).<\/p>\n<\/div><\/div>\n\"\"<\/p>\n

In our previous article, we see\u00a0how to\u00a0load data into Elastic Search using SSIS<\/a>. Now let’s look at how to read data from ElasticSearch using SSIS<\/strong>\u00a0and load response into SQL Server.<\/p>\n

Elasticsearch<\/a>\u00a0is a powerful engine that allows you to store, aggregate, and, most importantly, search data in a very analytical way. In this tutorial, you will learn how to load Elasticsearch\u00a0<\/em>data to\u00a0SQL Server <\/em>with\u00a0SSIS<\/acronym><\/a>\u00a0(part of SQL Server) and ZappySys PowerPack<\/a>. We also see how to set the maximum result window of the index using the Rest API Task.<\/p>\n

In a nutshell,\u00a0this post will focus on how to Make Elasticsearch Search API call using SSIS.<\/p>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Step-By-Step to Call Elasticsearch API call\u00a0using SSIS<\/span><\/span><\/h2>\n

    Read from Elasticsearch Search API call<\/h3>\n

    Let’s make the\u00a0Elasticsearch Search API<\/a> call using the JSON source to get all the records of the index with pagination.<\/p>\n

      \n
    1. First\u00a0of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.\n
    2. From the SSIS toolbox drag and drop JSON Source on the data flow designer surface.\n
    3. Double click JSON Source and enter the following URL as below to make\u00a0Search API Call on the index.\n
      http:\/\/{elasticsearch-site-url}\/{index}\/{index-type}\/_search<\/pre>\n<\/li>\n
    4. Now check Use credentials and\u00a0Select Connection<\/em> section press <New ZS-HTTP Connection><\/span>.\n
    5. Once the\u00a0HTTP Connection Manager<\/em> window opens configure the connection to your Elasticsearch instance:\n