ODBC PowerPack – Drivers for REST API, JSON, XML, SOAP…

OverviewWhat's new (Version History )System requirements
About ODBC PowerPack
ODBC PowerPack is a collection of high-performance ODBC API Drivers for various API data source (i.e. REST API, JSON, XML, CSV, Amazon S3 …). Using familiar SQL query language you can read/query data from API sources or JSON / XML / CSV Files inside  SQL Server (T-SQL) or your favorite Reporting (i.e. Power BI, Tableau, Qlik, SSRSMicroStrategy,  Excel, MS Access), ETL Tools  (i.e. Informatica, Talend, Pentaho, SSIS). You can also call our drivers from programming languages such as JAVA, C#, Python, PowerShell etc.

Using our API drivers you can consume data from local files or connect to virtually any API data source (internal or cloud API) such as Amazon AWS API, Salesforce API, Google API (i.e. Analytics, AdWords, DoubleClick), Facebook, Twitter, Zendesk, eBay and many more…

Download 30 days FREE Trial using below link

  • .Net framework 3.5
  • .Net framework 4.x (4.0 or higher)
  • Supported Client Operating Systems: Windows 7, Windows 8, Windows 8.1, Windows 10
  • Supported Server Operating Systems: Windows Server 2003/R2, Windows Server 2008/R2, Windows Server 2012/R2, Windows Server 2016

Main Features

  • ODBC 3.8 Compliant high performance drivers to integrate with virtually any API source or Local JSON/XML files.
  • Familiar SQL query engine to read/write data from API source (REST API, Web API, OData API)
  • Support for Basic Authentication, OAuth 1.0 and OAuth 2.0 API (Which means support for services like Office 365, Facebook, Twitter, Google BigQuery, AdWords, Salesforce, MailChimp… )
  • Support very large JSON / XML / CSV files
  • Fine tune driver performance Using many different options
  • Support for retry options (To handle intermittent failures)
  • Support for API pagination options (Many ways to paginate large API response)
  • Tight integration with SQL Server (Call REST API from T-SQL Code)
  • Support for most BI Reporting Tools like Power BI, Tableau, QlikExcel, SSRS, MS Access, MicroStrategy or Google Sheet and many more…
  • Support for ETL Tools like Informatica, SSIS, Pentaho, Talend and many more…
  • Access ZappySys drivers inside for popular programming languages such as JAVA, C#, PythonPowerShell
  • Call ZappySys drivers in JAVA apps (via JDBC) or Cross platform apps for OS like Unix / Linux / Mac / Ubuntu using Data Gateway Service

Which ODBC Drivers included?

Following Drivers are included in ODBC PowerPack. You can purchase single driver or buy the full pack (if available).

Driver Description
ODBC Driver for JSON File, REST, Web API JSON Files / REST API Driver

Read More
Must have ODBC Driver for JSON Files / REST API / OData Integration Projects. It can be used to read data from local JSON Files or any JSON based API Services (such as Google Analytics, BigQuery, Office 365 and many more). It supports high performance JSON Streaming engine with many options such as Pagination, Error handling, Security, Filtering.
ODBC Driver for XML File, REST, Web API XML Files / SOAP API Driver

Read More
Must have ODBC Driver for XML Files / SOAP API / OData Integration Projects. It can be used to read data from local XML Files or any XML / SOAP based API Services (such as Google AdWords, DoubleClick, Salesforce and many more). It supports high performance JSON Streaming engine with many options such as Pagination, Error handling, Security, Filtering.
ODBC Driver for CSV File, REST, Web API CSV Files / REST API Driver

Read More
Using this ODBC Driver you can read from CSV Formatted files or REST API which sends response in CSV format. It supports GZip or Zip format. If CSV data coming from URL you can use many advanced API settings such as Pagination, Error handling, Security.
Salesforce ODBC Driver Salesforce Driver

Read More
Salesforce ODBC Driver can be used to read data from Salesforce.com (Cloud CRM). Using this driver you can easily integrate salesforce data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort.
Amazon S3 ODBC Driver for CSV File Amazon S3 Driver for CSV Files

Read More
Amazon S3 ODBC Driver for CSV files can be used to read delimited files (e.g. CSV  / TSV ) stored in AWS S3 Buckets. Using this driver you can easily integrate AWS S3 data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
Azure Blob ODBC Driver for CSV File Azure Blob Driver for CSV Files

Read More
Azure Blob ODBC Driver for CSV files can be used to read delimited files (e.g. CSV  / TSV ) stored in Azure Blob Container. Using this driver you can easily integrate Azure blob data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
FTP / SFTP ODBC Driver for CSV File FTP / SFTP Driver for CSV Files

Read More
FTP / SFTP ODBC Driver for CSV files can be used to read delimited files (e.g. CSV  / TSV ) stored on FTP Sites (Classic FTP, SFTP or FTPS). Using this driver you can easily integrate FTP / SFTP data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
Amazon S3 ODBC Driver for JSON File Amazon S3 Driver for JSON Files

Read More
Amazon S3 ODBC Driver for JSON files can be used to read JSON Files stored in AWS S3 Buckets. Using this driver you can easily integrate AWS S3 data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
Azure Blob ODBC Driver for JSON File Azure Blob Driver for JSON Files

Read More
Azure Blob ODBC Driver for JSON files can be used to read JSON Files stored in Azure Blob Container. Using this driver you can easily integrate Azure blob data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
FTP / SFTP ODBC Driver for JSON File FTP / SFTP Driver for JSON Files

Read More
FTP / SFTP ODBC Driver for JSON files can be used to read JSON Files stored on FTP Sites (Classic FTP, SFTP or FTPS). Using this driver you can easily integrate FTP / SFTP data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
Amazon S3 ODBC Driver for XML File Amazon S3 Driver for XML Files

Read More
Amazon S3 ODBC Driver for XML files can be used to read XML Files stored in AWS S3 Buckets. Using this driver you can easily integrate AWS S3 data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
Azure Blob ODBC Driver for XML File Azure Blob Driver for XML Files

Read More
Azure Blob ODBC Driver for XML files can be used to read XML Files stored in Azure Blob Container. Using this driver you can easily integrate Azure blob data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
FTP / SFTP ODBC Driver for XML File FTP / SFTP Driver for XML Files

Read More
FTP / SFTP ODBC Driver for XML files can be used to read XML Files stored on FTP Sites (Classic FTP, SFTP or FTPS). Using this driver you can easily integrate FTP / SFTP data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
  Data Gateway Service

Read More
ZappySys Data Gateway Service provides connectivity to any ZappySys ODBC Drivers or other ODBC Drivers without installing driver on the server where you need to get the data. You can connect to ZappySys Gateway using any standard SQL Server Driver (ODBC, JDBC, OLEDB or ADO.net) and query data sources. You can also get cross platform connectivity using Data Gateway. It supports calling ZappySys drivers using JDBC from JAVA apps too.

Integration Scenarios (Reporting / ETL / BI / Programming)

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn’t appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.

ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

ZappySys ODBC Drivers – Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

BI / Reporting Tools
Integration

ETL Tools
Integration
Programming Languages
Integration

ODBC Integration Screenshots in various tools

  • Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
    Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData

ZappySys Data Gateway (ODBC Bridge for SQL Server / JAVA / Linux / Mac)

ZappySys has developed a unique bridge called ZappySys Data Gateway Service (ZSDG) which can help to access our Drivers in SQL Server or JAVA based Apps or Non-Windows OS (e.g. Mac, Linux). ZappySys Data Gateway service can run in the cloud (VM Exposed to internet) or you can install locally on-premises.

Client application can connect to Data Gateway Service using any Microsoft SQL Server compatible driver (i.e. SQL Server ODBC, OLEDB, ADO.net or JDBC Driver or Linked Server in SQL Server). Data Gateway can be installed on the central server where you can have many users who can connect to Data Gateway to use ZappySys Drivers without installing anything on their machine. Data Gateway Service understands TDS Protocol and Client App can be running on any machine or operating system (MacOS, Linux, Windows).

ZappySys Data Gateway - Connect to JSON, XML, OData, REST API, SOAP data sources using TDS protocol compatible drivers (or any SQL Server ODBC, JDBC, OLEDB, ADO.net driver )

ZappySys Data Gateway – Connect to JSON, XML, OData, REST API, SOAP data sources using TDS protocol compatible drivers (or any SQL Server ODBC, JDBC, OLEDB, ADO.net driver )

Microsoft SQL Server Integration using Data Gateway Service (T-SQL)

Here is one possible use case of using Data Gateway Service. Any DBA or Non-Programmer can start writing T-SQL queries to use ZappySys Drivers (e.g. REST API, JSON, XML, CSV data source) right inside your usual T-SQL code  (You can access data from Salesforce, REST API, JSON, XML, CSV inside Views, Functions or SQL Stored Procedures).

This approach can eliminate any possible ETL work needed to extract data outside of SQL Server, you can start using your existing SQL Skill to achieve previously hard to achieve scenarios without coding.

For many other possible use case of Data Gateway click here.

SQL Server Integration Example - Query REST API / JSON Files / XML Files inside SQL Server using ZappySys Data Gateway Service (Use of Linked Server / OPENQUERY Feature in T-SQL Code / SSMS)

SQL Server Integration Example – Query REST API / JSON Files / XML Files inside SQL Server using ZappySys Data Gateway Service (Use of Linked Server / OPENQUERY Feature in T-SQL Code / SSMS)

Video Tutorial – Calling ZappySys Drivers inside SQL Server (JSON / REST Driver use case)

Here is a short video to demonstrate a use case of Data Gateway. With this approach you can import data from REST API or any other data source for which ZappySys offers Drivers (e.g. Amazon S3, Azure, SFTP, Salesforce, XML , CSV)

Programming Language Examples

Most programming languages come with out of the box support for ODBC. Which means you can use ZappySys ODBC drivers inside your favorite language. Here are few languages which already support ODBC. We have used JSON Driver / SQL query as an example but concept is same for other drivers too. Refer to help file to learn more about Driver specific Connection String and SQL Query.

C#JAVAPythonPHPPowerShell
using (OdbcConnection conn = 
            new OdbcConnection("DRIVER ={ZappySys JSON Driver};DataPath='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json'"))
{
    conn.Open();
    cmd = new OdbcCommand(
@"SELECT 
Country as Invoice_Country, SUM(UnitPrice * Quantity) Total 
FROM value
GROUP BY Country
ORDERBY Total DESC", conn);
 
    var rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine("---- Fetching Row -------");
        for (i = 0; i < rdr.FieldCount; i++)
        {
            Console.Write("Field {0}={1} ", i, rdr[i]);
        }
        Console.WriteLine("");
    }
}
//Assuming the Microsoft SQL Server JDBC Driver is in below folder
//C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64
private static final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 
//The JDBC connection URL to connect to ZappySys Data Gateway Service using SQL Server driver
private static final String jdbcURL = "jdbc:sqlserver://localhost:5000;databasename=master;user=tdsuser;password=tds123;";
 
//Connect to the database
Connection databaseConnection = DriverManager.getConnection(jdbcURL);
System.out.println("Connected to ZappySys Data Gateway Service using Microsoft SQL Server JDBC driver");
 
//declare the statement object
Statement sqlStatement = databaseConnection.createStatement();
 
ResultSet rs = sqlStatement.executeQuery("SELECT Country , SUM(UnitPrice * Quantity) Total " 
	+ "FROM value " 
	+ "GROUP BY Country " 
	+ "WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')");
 
while (rs.next()) {
  System.out.println("-----Fetching new row----\n");	
  System.out.println(rs.getString("Country"+ "\n");
  //System.out.println(rs.getString("Total") + "\n");
}
#Example of using ODBC driver inside Python using pyodbc library (Read more info about pyodbc from below)
#https://github.com/mkleehammer/pyodbc/wiki

import pyodbc 
 
#connect to api service using ZappySys ODBC driver for JSON

#Use DSN 
#conn = pyodbc.connect(r'DSN=MyZappyDsnName;')

# OR Use direct connection string 
conn = pyodbc.connect(
    r'DRIVER={ZappySys JSON Driver};'
    )
cursor = cnxn.cursor()	
 
#execute query to fetch data from API service
cursor.execute("SELECT * FROM value ORDER BY Country WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')") 
row = cursor.fetchone() 
while row: 
    print row[0] 
    row = cursor.fetchone()


echo "Example of using ZappySys ODBC Driver in PHP\n";
 
$conn = odbc_connect("DRIVER={ZappySys JSON Driver};""""");
$sql = "SELECT * FROM value ORDER BY Country WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')";
$rs = odbc_exec($conn,$sql);
 
echo "Fetching first row....\n";
odbc_fetch_row($rs);
echo "Country=" . odbc_result($rs,"Country") . "\n";
 
echo "Closing connection ....\n";
odbc_close($conn);


$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DRIVER={ZappySys JSON Driver}"
 
#--OR-- Use DSN name
#$conn.connectionstring = "DSN=MyDSNName"
 
$conn.Open()
 
# -------------------------------------------------------------------------------
# In powershell $ is special char so we used `$ in below string to escape it. 
# Also We used multi string start with "@<new line> and ends with <new line>"@
# -------------------------------------------------------------------------------
$sql = 
@"
SELECT * FROM value 
WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Customers?`$format=json')
"@
 
$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql
 
$dataset = New-Object System.Data.DataSet
#Load data in DataSet
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)
 
#Export datatable to file in CSV format
$dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter "`t" | Out-File "c:\temp\dump.csv" -fo
 
Write-Host "Total rows $($dataSet.Tables[0].Rows.Count)"
$conn.Close()

SQL Query Language Examples

ZappySys introduced most innovative way to transform your complex API into Flat table using familiar SQL Query Language. Here are some examples of SQL Queries our drivers understand.

/* SQL Language Support to query API or JSON/XML Files */
SELECT Country as Invoice_Country, SUM(UnitPrice * Quantity) Total 
FROM value 
WHERE Discount > 0
GROUP BY Country
HAVING SUM(UnitPrice * Quantity)>1000
ORDER BY Total DESC
WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')

/* Query JSON/XML File(s) */
SELECT * FROM $ WITH (SRC=@'C:\Data\Customer*_??.json')

/* Query API Service - GET Request */
SELECT * FROM value 
WITH (SRC=@'http://services.odata.org/V3/Northwind/Northwind.svc/Order_Details?$format=json'
,DataFormat='OData'
)

/* Submit data to API - POST Request */
SELECT * FROM $
(METHOD='POST' ,HEADER='Content-Type:text/plain||x-hdr1:AAA'
,SRC='http://httpbin.org/post'
,BODY=@'@c:\files\dump.json'
,IsMultiPart='True'
)

 

REST / SOAP API Call Settings

Our drivers come with easy to use API query builder with many advanced options to connect to virtually any JSON, XML or CSV based data format from local files or API URL.

ZappySys ODBC Driver UI - setting to read from File or API URL. Read from JSON REST API / XML SOAP Web Service / CSV Format.

ZappySys Driver UI – setting to read from File or API URL. Read from JSON REST API / XML SOAP Web Service / CSV Format.

SQL Query Editor / Live Preview / Examples

Our drivers come with easy to use Query editor so you can test any API

ODBC Driver - SQL Query Editor / Live Preview

ODBC Driver – SQL Query Editor / Live Preview

Security Standards (OAuth, JWT, Basic Auth, X509)

Our ODBC Drivers tested with wide variety of security standards such as OAuth 1.0, OAuth 2.0, Basic Auth, JWT, X509 Certificate. We also support latest encryption standards for secure http (such as SSL/TLS 1.2)

Here is an example of our easy to use OAuth Connection Manager which can be used to connect to any API with OAuth 1.0 or OAuth 2.0 Standards.

OAuth Connection for API Providers (Such as Facebook, Google, Twitter, Linkedin, Amazon, Hubspot, MailChimp...)

OAuth Connection for API Providers (Such as Facebook, Google, Twitter, Linkedin, Amazon, Hubspot, MailChimp…)

 

Consume data from virtually any API service

Our ODBC API Drivers for JSON, XML and CSV comes with more than 100+ Advanced options to connect to virtually API. Below are few Data sources you may recognize which we have tested to work with our drivers. If your API is not listed below then feel free to contact our Data Integration Team (via support form)  and get your question answered quickly.

REST / OData / SOAP API

XML Files

Salesforce API

Json Logo

JSON Files

 Amazon AWS S3 Storage Service Logo - File storage in cloud

Amazon AWS API

Google Analytics Logo

Google Analytics API

Google BigQuery API Integration

Google BigQuery API

ElasticSearch API Integration

ElasticSearch API

Xero API Integration

Xero API

eBay API Integration

eBay API

Xero API Integration

Zendesk API

eBay API Integration

MailChimp API

Google DoubleClick API Integration

Google DoubleClick API

HubSpot API Integration

HubSpot API

WorkDay API Integration

WorkDay API

Google AdWords API Integration

Google AdWords API

Google Sheet API Integration

Google Sheet API

SurveyMonkey API Integration

SurveyMonkey API

Facebook API Integration

Facebook API

Twitter API Integration

Twitter API

Graph API / Office 365 API Integration

Office 365 API

ServiceNow REST API Integration

ServiceNow API

Step-By-Step Video Tutorial (6 Parts Video Series)

Here is full length tutorial if you like to get started with ZappySys Drivers. Below tutorial shows usecase of REST API integration in Excel and Power BI. It covers many topics such as below. We used JSON Driver as an example but many concepts mentioned in below tutorial can be applicable to all other ZappySys drivers.

  • Configure ODBC DSN
  • Making HTTP GET Calls
  • Authenticating REST API requests
  • Pagination in REST API
  • Building simple and advanced SQL Queries
  • POST data to REST API
  • Excel Example – Import REST API in Excel
  • Power BI Example – Import REST API in Power BI

What people say about ZappySys