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 4.x (any 4.x version such as 4.0, 4.5, 4.6 …)
  • Desktop OS – Windows 11, Windows 10, Windows 8, Windows 7
  • Server OS – Windows Server 2022, Windows Server 2019, Windows Server 2016, Windows Server 2012 (R2), Windows Server 2008, Windows Server 2003
  • For JDBC Bridge Driver – You will need Java8 JRE (Runtime) or JDK (Development Kit). You can Download from here (See official page here )

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
  • Allow to use Custom SQL for Virtual Table
  • Pre-Configured brand new API Connectors Library are now available with our brand new API Driver (Pre-Configured API Connectors)
  • API Driver support Enhance Query Builder to add column list / key in WHERE for SELECT, UPDATE , INSERT, DELETE
  • User Defined Functions bytes_to_base64, text_to_base64, base64_to_text and file_to_base64
  • Supports window functions (e.g. ROW_NUMBER , RANK, PERCENT_RANK, NTILE, NTH_VALUE, LEAD, LAST_VALUE, LAG, FIRST_VALUE, DENSE_RANK, CUME_DIST)

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 API API Driver (Pre-Configured API Connectors)

Read More
API ODBC driver can read / write data from many popular online services (full list here) using familiar SQL language without learning complexity of REST API calls. The driver allows querying nested structure and output as a flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.
ODBC Driver for JSON File, REST, Web API JSON Files / REST API Driver (Generic)

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 (Generic)

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 (Generic)

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).
JDBC Bridge ODBC Driver JDBC Bridge Driver

Read More
JDBC 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. If your data source offers only JDBC driver but you like to consume in non-JAVA apps with ODBC connectivity option then you can use this driver. You can Read/write data using familiar SQL language in your favorite apps / programming languages.
ODBC Driver for Excel File Excel Driver

Read More
Using this ODBC Driver you can read from Excel files.
  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='https://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='https://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='https://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='https://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='https://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.

To see all examples check this link (JSON / REST examples) Select correct driver node in help file – e.g. JSON here, XML here, CSV here

Familiar SQL Syntax (Where, Group By, Order By)

/* 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='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')

Read from Local File

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

GET data from URL (Read Example)

SELECT * FROM value 
WITH ( 
METHOD='GET', 
SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json' 
)

POST data to URL (Write Example)

/* Submit data to API - POST Request. You can POST data from file content or supply body as direct string.*/
SELECT * FROM $  
WITH 
(  
 METHOD='POST'  
,HEADER='Content-Type:text/plain||x-hdr1:AAA'  
,SRC='http://httpbin.org/post'  
,BODY='@c:\files\dump.json'  
,IsMultiPart='True'  
)

API Pagination – Paginate based on URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. Driver keeps incrementing page number and calls next URL until last page is detected. There are few ways to indicate last page (e.g. By status code, By row count, By response size). If you don’t specify end detection then it will use default (i.e. No records found).

SELECT * FROM $
WITH (
SRC='https://zappysys.com/downloads/files/test/cust-<%page%>.json'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

Advanced URL JOIN – Pass parent URL data to Child URL

This example shows how to perform URL JOIN. Using this pattern you can pass parent URL data to child API URL in one query. You can pass parent data in child URL or Body (for POST). Placeholder syntax is [$parent_alias.parent_col_name$]. Each level must have ALIAS attribute. For root level you dont have to Prefix JOINxxxxxxx but for any other level you must Prefix JOINxxxxx before each attribute name. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

SELECT
c.custid,c.name,c.city
,o.orderid,o.orderdate,o.total
/* ,i.itemid,i.qty,i.shipped */
FROM $
WITH(
--1st level (root)--
Src='https://zappysys.com/downloads/files/test/join/customers.json'
,NextUrlAttributeOrExpr='$.nextpage'
,Filter='$.customers[*]'
,Alias='c'
,WaitTimeMs='500' --2nd level-- --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] )
,Join1_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/orders.json'
,Join1_filter='$.orders[*]'
,Join1_alias='o'
,Join1_NextUrlAttributeOrExpr='$.nextpage'
,Join1_WaitTimeMs='500'
/*
--3rd level--
--You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] and [$o.orderid$])
,Join2_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/o[$o.orderid$]/items.json'
,Join2_filter='$.items[*]'
,Join2_alias='i'
,Join2_NextUrlAttributeOrExpr='$.nextpage'
,Join2_WaitTimeMs='500'
--maximum 7 levels deep you can go. Any attributes allowed in WITH clause can be prefixed by by joinN_xxx
-- join3_xxxxx, join4_xxxxxx .......... join7_xxxxx
*/
)

URL JOIN Pattern – Download files to local disk / Use functions

This example shows how to download files to local disk. It first gets records by calling first URL and then pass it to seconds URL. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

SELECT a.url,b.data,file_write_binary(base64_to_bytes(b.data) ,'c:\temp\' || name  ) as bytes_written
from $
WITH (
SRC='https://zappysys.com/downloads/files/test/imagelist.json',
FILTER='$.list[*]',
ALIAS='a',

JOIN1_SRC='[$a.url$]',
JOIN1_ALIAS='b',
JOIN1_SaveContentAsBinary='true'
)

Query Large Compressed File (From URL or Local disk)

This example shows how to read very large file from local disk or URL. You can read uncompressed or compressed file (in our example its GZip compressed file). Using –FAST option in your Filter enables Streaming Mode. To use STREAM Mode you must turn off IncludeParentColumns option as per the example.

SELECT * FROM $
--LIMIT 10
WITH(
Filter='$.LargeArray[*]--FAST' --//Adding --FAST option turn on STREAM mode (large files)
,SRC='https://zappysys.com/downloads/files/test/large_file_10k_largearray_prop.json.gz'
--,SRC='C:\Data\large_file*.json.gz'
,IncludeParentColumns='False'  --//This Must be OFF for STREAM mode (read very large files)
,FileCompressionType='GZip' --Zip or None (Zip format only available for Local files)
)

Support for various authentication types

ZappySys Drivers support various authentication types

Option Description
Notset Not set
Windows Default Windows Credentials – Current User Credentials
Basic Basic – UserID/Password
NTLM NTLM – UserID/Password/Domain
Kerberos Kerberos – UserID/Password/Domain
SoapWss SOAP WSS – UserID/Password in SOAP Body
Token Static Token / API Key
TokenDynamic Dynamic Token – Two Step Auth (i.e. Login to get token)
TokenJwt JWT Token (RFC 7519)
HashSignature HMAC Signature (i.e. Request Hashing using Key / Password)

 

Custom 2 Step Auth – Dynamic Token Example

This example shows how to call REST API Service using Dynamic Token approach. Learn how to authenticate using userid / password to obtain temporary token which can be passed to call API. Also learn how to pass Body, Headers using HTTP POST. For more information about Dynamic Token visit https://zappysys.com/links/?id=10091

SELECT * FROM $
WITH(
Src='https://zappysys.com/downloads/files/test/rest-getdata.aspx?format=json'
--,RequestMethod='POST'
--,RequestData='{sessionid: "[$token$]"}'
,Filter='$.rows[*]'
,Header='Content-Type: application/json || Accept: */* || Cache-Control: no-cache'
,DataConnectionType='HTTP'
,CredentialType='TokenDynamic'
,UserName='MyUser001'
,Password='P@$$w0rdAAc12'
,AuthScheme='{none}'
,TokenUrl='https://zappysys.com/downloads/files/test/rest-login.aspx'
,TokenRequestData='{ user:"[$userid$]", password:"[$password$]" }'
,TokenRequestMethod='POST'
,TokenResponseContentFilter='$.sessionid'
,TokenRequestHeaders='Content-Type:application/json | Accept:*/* || Cache-Control:no-cache'
,TokenResponseContentType='Json'
)

Basic Authentication Example (Userid / password based)

SELECT * FROM $
WITH (
SRC='http://httpbin.org/basic-auth/user1/pass123',
DataConnectionType='HTTP',
CredentialType='Basic',
UserName='user1',
Password='pass123'
)

OAuth 2.0 Authentication Example

SELECT * FROM $ WITH
(
SRC='https://www.googleapis.com/analytics/v3/metadata/ga/columns'
,UseCustomApp='True'
,DataConnectionType='OAuth'
,ServiceProvider='Google'
,ClientId='3855xxxxxx-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com'
,ClientSecret='qF7axxxxxxxxxxxWfs'
,AccessToken='ya29.GlsxxxxxxxxxIam'
,RefreshToken='1/pFOxxxxxxxxxxgU'
)

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