How to connect ElasticSearch to SSRS

Introduction

You can connect to your ElasticSearch data from SSRS via the high-performance ElasticSearch ODBC Driver. We'll walk you through the entire setup.

Let's not waste time and get started!

Video tutorial

Watch this quick video to see the integration in action. It walks you through the end-to-end setup, including:

• Installing the ODBC PowerPack
• Configuring a secure connection to ElasticSearch
• Working with ElasticSearch data directly inside SSRS
• Exploring advanced API Driver features

Ready to dive in? Download the product to jump right in, or follow the step-by-step guide below to see how it works.

Create data source in ZappySys Data Gateway

In this section we will create a data source for ElasticSearch in the Data Gateway. Let's follow these steps to accomplish that:

1. Download and install ODBC PowerPack (if you haven't already).

2. Search for gateway in the Windows Start Menu and open ZappySys Data Gateway Configuration:

   

3. Go to the Users tab and follow these steps to add a Data Gateway user:

   • Click the Add button
   • In the Login field enter a username, e.g., john
   • Then enter a Password
   • Check the Is Administrator checkbox
   • Click OK to save
   

4. Now we are ready to add a data source:

   • Click the Add button
   • Give the Data source a name (have it handy for later)
   • Then select Native - ZappySys API Driver
   • Finally, click OK
   ElasticsearchDSN

   ZappySys API Driver

   

5. When the Configuration window appears give your data source a name if you haven't done that already, then select "ElasticSearch" from the list of Popular Connectors. If "ElasticSearch" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

   ElasticsearchDSN

   ElasticSearch

   

6. Select your authentication scenario below to expand connection configuration steps to:

   • Configure the authentication in ElasticSearch.
   • Enter those details into the ZappySys API Driver data source configuration.
   Basic Authentication (UserId/Password)

   ElasticSearch authentication

   Use HTTP Basic Authentication with any Elasticsearch-compatible cluster (self-hosted, Elastic Cloud, OpenSearch, or other managed hosts).

   1. Set Service URL to your cluster base URL (for example https://your-host:9200 or the HTTPS URL from your provider).
   2. Enter User Name and Password for a user that can call the Elasticsearch REST API. Some managed hosts label these as Access Key / Access Secret — use those values in the same fields.
   3. If you use a self-signed certificate and see a trust error, set Ignore certificate related errors to True (only if you trust the server).
   4. Click Test connection.

   Optional — Bonsai example: On Bonsai, open https://app.bonsai.io/clusters/{your-instance-id}/tokens, copy Access Key and Access Secret into User Name and Password, then test. Sample data (if needed): https://{your-cluster-id}.apps.bonsaisearch.net/app/home#/tutorial_directory.

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to Basic Authentication (UserId/Password) [Http]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Finally, hit OK button:

   ElasticsearchDSN

   ElasticSearch

   Basic Authentication (UserId/Password) [Http]

   http://localhost:9200

   Optional Parameters
   User Name (or Access Key)
   Password (or Access Secret)
   Ignore certificate related errors

   

   Find full details in the ElasticSearch Connector authentication reference.

   Windows Authentication (No Password)

   ElasticSearch authentication

   No instructions available.

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to Windows Authentication (No Password) [Http]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Finally, hit OK button:

   ElasticsearchDSN

   ElasticSearch

   Windows Authentication (No Password) [Http]

   http://localhost:9200

   Optional Parameters
   Ignore certificate related errors

   

   Find full details in the ElasticSearch Connector authentication reference.

7. Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

   

   ZappySys API Driver - ElasticSearch

   Read and write Elasticsearch data effortlessly. Integrate, manage, and automate indexes and documents — almost no coding required.

   ElasticsearchDSN

   

8. Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in SSRS to retrieve data from ElasticSearch. Hit OK button to use this query in the next step.

   SELECT * FROM Indexes

   
   Some parameters configured in this window will be passed to the ElasticSearch API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.

9. Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in SSRS:

   

   ZappySys API Driver - ElasticSearch

   Read and write Elasticsearch data effortlessly. Integrate, manage, and automate indexes and documents — almost no coding required.

   ElasticsearchDSN

   SELECT * FROM Indexes

   
   You can also access data quickly from the tables dropdown by selecting <Select table>.
   A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the ElasticSearch API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in ElasticSearch servers).

10. Click OK to finish creating the data source.

11. Once done, go to the Network Settings tab and Add a firewall rule for inbound traffic:

    
    • This will initially allow all inbound traffic.
    • Click Edit IP filters to restrict access to specific IP addresses or ranges.

12. Crucial Step: After creating or modifying the data source, you must:

    • Click the Save button to persist your changes.
    • Hit Yes when prompted to restart the Data Gateway service.

    This ensures all changes are properly applied:

    
    Skipping this step may cause the new settings to fail, preventing you from connecting to the data source.

Read data in SSRS from ZappySys Data Gateway

1. Open Visual Studio and create a new SSRS project.

2. Then add a new Shared Data Source (you can create a non-shared data source inside report too):

   

3. Continue with creating the Shared Data Source. Select Microsoft SQL Server as Type and hit Build button to proceed further:

   

4. Once a window opens, configure it similarly. Configure "ElasticsearchDSN" as database name. Finally, hit Test Connection and OK:

   ElasticsearchDSN

   

5. Another window opens, and it should look similarly to this one below which ends the creation of a Data Source:

   DataSource=localhost,5000;Initial Catalog=ElasticsearchDSN

   

6. Now it's time to create a Dataset. If you don't have a report created, in one of the wizard's steps it will look like this:

   SELECT * FROM Indexes

   

7. Finally, once you complete the report, similar results will show up:

   

Passing Parameters to SSRS Report / Filter data

If you want to parameterize your report, then refer to this article

ElasticSearch Connector actions

Got a specific use case in mind? We've mapped out exactly how to perform a variety of essential ElasticSearch operations directly in SSRS, so you don't have to figure out the setup from scratch. Check out the step-by-step guides below:

• Create Index
• Delete Index
• List indexes
• List aliases
• Get Index or Alias metadata
• Get documents from Index or Alias
• Get document by ID from Index or Alias
• Search / Query documents
• Count documents
• Insert documents
• Upsert documents
• Update documents
• Delete documents
• Make Generic REST API Request
• Make Generic REST API Request (Bulk Write)