JDBC-ODBC Bridge Connector for SSRS

JDBC-ODBC Bridge driver can be used to consume data from any JDBC Driver in non JAVA apps (i.e. Excel, Power BI, C#). Many apps written in C++ or .net (e.g. Excel, Power BI, Informatica) which don’t have direct support for using JAVA based JDBC driver technology.

In this article you will learn how to quickly and efficiently integrate JDBC-ODBC Bridge data in SSRS without coding. We will use high-performance JDBC-ODBC Bridge Connector to easily connect to JDBC-ODBC Bridge and then access the data inside SSRS.

Let's follow the steps below to see how we can accomplish that!

Download Documentation

Video Tutorial - Integrate JDBC-ODBC Bridge data in SSRS

This video covers the following topics and more, so please watch carefully. After watching the video, follow the steps outlined in this article:

  • How to download and install the required PowerPack for JDBC-ODBC Bridge integration in SSRS
  • How to configure the connection for JDBC-ODBC Bridge
  • Features of the ZappySys JDBC Bridge Driver (Authentication / Query Language / Examples / Driver UI)
  • How to use the JDBC-ODBC Bridge in SSRS

Prerequisites

Before we begin, make sure you meet the following prerequisite: Java Runtime Environment (JRE) or Java Development Kit (JDK) must be installed on your system.

If your JDBC Driver targets a different Java version (e.g., 11 / 17 / 21), install the corresponding or newer Java version.

If you already have a JRE installed, you can try using it too. However, if you experience any issues, we recommend using one of the distributions mentioned above (you can install an additional JRE next to the existing one; just don't forget to configure the default Java in the Windows Environment Variables).

Download your Java application JDBC driver

To connect to your Java application in , you will have to download JDBC driver for it, which we will use in later steps. Let's perform these little steps right away:

  1. Visit your Java application website or MVN Repository.
  2. Download the JDBC driver, and save it locally, e.g. to D:\Drivers\JDBC\app-jdbc.jar.
  3. Done! That was easy, wasn't it? Let's proceed to the next step.

Create Data Source in Data Gateway based on ZappySys JDBC Bridge Driver

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

  1. Download and install ODBC PowerPack.

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

    Opening Data Gateway

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

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

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

    • Click Add button
    • Give Datasource a name (have it handy for later)
    • Then select Native - ZappySys JDBC Bridge Driver
    • Finally, click OK
    JdbcOdbcBridgeDSN
    ZappySys JDBC Bridge Driver
    Data Gateway - Adding data source

  5. Now, we need to configure the JDBC connection in the new ODBC data source. Simply enter the Connection string, credentials, configure other settings, and then click Test Connection button to test the connection:

    JdbcOdbcBridgeDSN
    jdbc:exampleApp://app-service-host-name.com:1234
    com.provider.exampleApp.jdbc.AppDriver
    D:\Drivers\JDBC\app-jdbc.jar
    admin
    *************
    [{"Name":"AppParameter","Value":"12345"}]
    JDBC-ODBC Bridge driver data source settings

    Use these values when setting parameters:

    • Connection string: jdbc:exampleApp://app-service-host-name.com:1234
    • Driver class: com.provider.exampleApp.jdbc.AppDriver
    • JDBC driver file(s): D:\Drivers\JDBC\app-jdbc.jar
    • User name: admin
    • User password: *************
    • Connection parameters: [{"Name":"AppParameter","Value":"12345"}]

  6. You should see a message saying that connection test is successful:

    ODBC connection test is successful

    Otherwise, if you are getting an error, check out our Community for troubleshooting tips.

  7. We are at the point where we can preview a SQL query. For more SQL query examples visit JDBC Bridge documentation:

    JdbcOdbcBridgeDSN
    SELECT * FROM orders
    JDBC ODBC Bridge data source preview 
    SELECT * FROM orders
    You can also click on the <Select Table> dropdown and select a table from the list.

    The ZappySys JDBC Bridge Driver acts as a transparent intermediary, passing SQL queries directly to the JDBC driver, which then handles the query execution. This means the Bridge Driver simply relays the SQL query without altering it.

    Some JDBC drivers don't support INSERT/UPDATE/DELETE statements, so you may get an error saying "action is not supported" or a similar one. Please, be aware, this is not the limitation of ZappySys JDBC Bridge Driver, but is a limitation of the specific JDBC driver you are using.

  8. Click OK to finish creating the data source.

  9. Very important step. Now, after creating or modifying the data source make sure you:

    • Click the Save button to persist your changes.
    • Hit Yes, once asked if you want to restart the Data Gateway service.

    This will ensure all changes are properly applied:

    ZappySys Data Gateway - Save Changes
    Skipping this step may result in the new settings not taking effect and, therefore you will not be able to connect 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):

    Adding a Data Source in SSRS project

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

    Configuring a Data Source to access API data in SSRS project

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

    JdbcOdbcBridgeDSN
    Configuring thoroughly a Data Source to access API data in SSRS project

  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=JdbcOdbcBridgeDSN
    How Data Source in SSRS project looks like

  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 orders
    Specifying a query in SSRS Dataset to access API data #1

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

    ZappySys Data Gateway API data source results

Passing Parameters to SSRS Report / Filter data

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

Troubleshooters & resources (JDBC Bridge Driver)

Below are some useful community articles to help you troubleshoot and configure the ZappySys JDBC Bridge Driver:

Conclusion

In this article we showed you how to connect to JDBC-ODBC Bridge in SSRS and integrate data without any coding, saving you time and effort. ZappySys JDBC Bridge Driver allows you to connect to any Java application that supports JDBC.

We encourage you to download JDBC-ODBC Bridge Connector for SSRS and see how easy it is to use it for yourself or your team.

If you have any questions, feel free to contact ZappySys support team. You can also open a live chat immediately by clicking on the chat icon below.

Download JDBC-ODBC Bridge Connector for SSRS Documentation

More integrations

Other connectors for SSRS

All
Big Data & NoSQL
Database
CRM & ERP
Marketing
Collaboration
Cloud Storage
Reporting
Commerce
API & Files

Other application integration scenarios for JDBC-ODBC Bridge

All
Data Integration
Database
BI & Reporting
Productivity
Programming Languages
Automation & Scripting
ODBC applications
Download JDBC-ODBC Bridge Connector for SSRS Documentation