{"id":943,"date":"2017-01-17T00:22:47","date_gmt":"2017-01-17T00:22:47","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=943"},"modified":"2025-03-18T13:43:15","modified_gmt":"2025-03-18T13:43:15","slug":"call-elasticsearch-api-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/call-elasticsearch-api-using-ssis\/","title":{"rendered":"How to call Elasticsearch API 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 learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which 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, Informatica).<\/p>\n<\/div><\/div>\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 bulk load data from SQL Server to Elasticsearch\u00a0<\/em>with\u00a0SSIS<\/acronym><\/a>\u00a0(part of SQL Server) and ZappySys PowerPack<\/a>.<\/p>\n

The scope of this article will be to show how to import records from SQL Server into Elasticsearch index as JSON documents using Elasticsearch Bulk API<\/a>. In a nutshell, we will retrieve IDs and names of the products from a SQL table, transform each row into a JSON and lastly, index each JSON in Elasticsearch under record’s corresponding ID. This tutorial is going to be your stepping stone to use\u00a0any\u00a0Elasticsearch API as a destination.<\/p>\n

We will be using these SSIS components of PowerPack to make things work:<\/p>\n

\n\n\n\n\n
\"\"<\/a><\/td>\nJSON Generator Transform<\/a><\/td>\n<\/tr>\n
\"\"<\/a><\/td>\nWeb API Destination<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Let’s begin?<\/p>\n

Prerequisites<\/h2>\n
    \n
  1. SSIS designer installed.\u00a0Sometimes it is referred as BIDS or SSDT (download it from Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. Northwind<\/em> database deployed on your machine (we will use it to load data from; download it<\/a>).<\/li>\n
  4. Elasticsearch\u00a0<\/i>instance up and running<\/a>.<\/li>\n
  5. ZappySys SSIS PowerPack<\/em> installed<\/a>.<\/li>\n<\/ol>\n
    NOTE:<\/strong> If your ElasticSearch instance is hosted as AWS Managed ElasticSearch Instance<\/a> then select\u00a0\u00a0<New ZS-OAUTH Connection><\/span>. rather than ZS-HTTP (Explained later in this article). When OAuth UI launches select AWS v4 Provider. For more information on calling REST API on AWS check this article<\/a>.\u00a0<\/div><\/div>\n

    Step-by-Step\u00a0\u2013 Bulk loading data from SQL Server\u00a0to Elasticsearch with SSIS<\/h2>\n

    Load data from SQL Server database first<\/h3>\n

    In this section you will perform several basic steps to start the package:\u00a0load data from SQL Server and prepare it for further steps.<\/p>\n

      \n
    1. Create a new SSIS package and drag a\u00a0Data Flow Task<\/em>\u00a0into the Control Flow<\/em>\u00a0from the SSIS Toolbox.\n
      \"\"

      Dragging and dropping Data Flow Task into Control Flow<\/p><\/div><\/li>\n

    2. Open Data Flow Task<\/em> and then add OLE DB Source.<\/em><\/li>\n
    3. Configure OLE DB Source to take data from Products <\/em>table,\u00a0Northwind<\/em> database.<\/li>\n
    4. Choose ProductID<\/em> and ProductName<\/em> as columns you will use.<\/li>\n<\/ol>\n
      \"Load<\/a>

      Load data to Elasticsearch using SSIS and ZappySys Web API Destination component.<\/p><\/div>\n

      Transform data into JSON documents<\/h3>\n

      To add data into Elasticsearch firstly we need to prepare it with JSON Generator Transform. <\/em>It will help us convert table rows into JSON documents.<\/p>\n

      JSON String to Create Index data for the Bulk API call<\/h4>\n
        \n
      1. Close the window and then drag Derived Column\u00a0<\/em>component from the SSIS Toolbox.<\/li>\n
      2. Add two new columns and name them\u00a0RowHeader<\/span>\u00a0and\u00a0RowFooter<\/span>:\n
        \"Derived<\/a>

        Derived Column configuration to add prefix and header and footer to a JSON<\/p><\/div>\n

        In the Expression<\/em> column give them values:<\/p>\n