ODBC API Driver

API ODBC Driver – For Online Services

API ODBC driver can read / write data from many popular online services (see the 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.

This driver is using same high performance data processing engine which was originally developed for JSON / REST Connector in SSIS PowerPack.

Both products( i.e. ODBC PowerPack and SSIS PowerPack)  share many UI elements and concepts. We wrote many articles to explain various features in one product but concepts are mostly same in both products so hope you can reuse steps explained in different articles even though screenshots /steps may be slightly different.

Feature Summary

  • In few clicks read /write data from popular online services using familiar SQL Query language (e.g. Google BigQuery, Sheets, SharePoint, JIRA, Zendesk and more…)
  • No need to learn Complex API Configurations / Pagination / Authentication details about REST API
  • Intuitive UI for easy step by step configuration without having API knowledge
  • Easily modify existing connector file based on your need.
  • Allows to create and use your own reusable connector file for custom API (or you can consider using our generic JSON Driver or XML Driver)
  • Integrate insight many ODBC Compliant Apps for Reporting / ETL / Database (e.g. Power BI, Informatica, MS Access, Excel, SSRS and more…)
  • Support for programming languages (e.g. JAVA, C#, Python, PowerShell and more…)
  • Tight integration with Microsoft SQL Server (With support for Gateway Option – No need to install Driver on Server)
  • Familiar SQL Query language support including WHERE, ORDER BY, GROUP BY constructs
  • Support for custom math/ string / datetime / JSON /XML functions in SQL query Language
  • The unparalleled performance with large file support
  • Latest security standards such as OData,  OAuth and JWT (RFC 7519) (Call services like Google API, Facebook …)
  • REST API Pagination Support to consume multiple requests / loop
  • HTTPS / SSL / TLS 1.2 Support for more secure communication
  • Support for Proxy Server

Download Help File Buy
View All Drivers

Prebuilt Connector Files

Featured Articles

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 Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

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

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

SQL Query Examples – API ODBC Driver

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')


/*--------- INSERT---------*/
INSERT INTO MyTable(Col1,Col2) Values('something', 1234)

/*--------- UPDATE---------*/
UPDATE MyTable SET Col1='something', Col2=1234

/*--------- DELETE---------*/
DELETE FROM MyTable

/*--------- UPSERT---------*/
UPSERT INTO MyTable(Col1,Col2) Values('something', 1234)
/*--------- Bulk INSERT (Read from MSSQL, ODBC) - SOURCE Clause---------*/
INSERT INTO MyTable
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')
WITH(MyParam1='something', MyParam2='something')
/*--------- Bulk DELETE (Read from MSSQL, ODBC) - SOURCE Clause---------*/
DELETE FROM MyTable
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')
WITH(MyParam1='something', MyParam2='something')
/*--------- Bulk UPDATE (Read from MSSQL, ODBC) - SOURCE Clause---------*/
UPDATE MyTable
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')
WITH(MyParam1='something', MyParam2='something')
/*--------- Using Connection Clause ---------*/
SELECT * FROM Customers
--WITH(Param1 = 'something', Param2 = 'something')
CONNECTION(
    Provider = 'OData'
    , ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
--, Provider = 'Custom'
--, ConfigFile = 'C:\connectors\odata-connector.xml'
--, AuthName = 'BasicAuth'
--, UserName = 'user1'
--, Password = 'pass1'
--, Parameters = '[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'
)
/*--------- Using Direct ConfigText in Connection Clause ---------*/
SELECT * FROM  Customers
--WITH(Param1='something', Param2='something')
CONNECTION(
--	,Provider='OData'
	,ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc'
	,Provider='Custom'
	,UseEmbeddedConfig=1
    ,ConfigText='<ApiConfig>
  <ServiceUrls>
    <ServiceUrl Url="https://services.odata.org/V3/Northwind/Northwind.svc" />
  </ServiceUrls>
  <EndPoints>
    <EndPoint Name="Customers" Url="/Customers?$format=json" Method="GET" Filter="$.value[*]" Body="" />
  </EndPoints>
</ApiConfig>'	
--	,ConfigFile='C:\connectors\odata-connector.xml'  --to use file mode remove UseEmbeddedConfig, ConfigText and set Provider='Custom'
--	,AuthName='BasicAuth'	
--  ,UserName='user1'
--  ,Password='pass1'
--	,Parameters='[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'
)
/*--------- Filter on Date, Group By, Order By, Limit Clause ---------*/
SELECT
  Country AS Invoice_Country,
  SUM(UnitPrice * Quantity) AS Invoice_Total
FROM
  Invoices
WHERE
  Discount > 0
  AND OrderDate <= DATETIME('1997-12-31 00:00:00') --or use OrderDate<=DATE('1997-12-31')
  --Valid formats are yyyy-MM-dd, yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.fff (where fff is milliseconds)
GROUP BY
  Country
HAVING
  SUM(UnitPrice * Quantity) > 1000
ORDER BY
  Invoice_Total DESC --,DATETIME(OrderDate)
LIMIT
  3 CONNECTION(
    Provider = 'OData', ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
  );
/*--------- Case Statement ---------*/
SELECT
  CustomerID,
  CompanyName,
  Country,
  Region --Pattern#1 - Match by clause,
  CASE WHEN Country IN('UK', 'Germany', 'France')
  OR Region = 'BC' THEN 'EU' WHEN Country IN('USA', 'Maxico', 'Canada') THEN 'NA' WHEN Country IN('Brazil', 'Argentina', 'Venezuela') THEN 'SA' ELSE 'Unknown' END Pattern1 --Pattern#2 - Match by static value or simple expression,
  CASE Country WHEN 'U' + 'K' THEN 'EU' WHEN 'USA' THEN 'EU' WHEN 'Brazil' THEN 'SA' WHEN 'Argentina' THEN 'SA' WHEN 'Venezuela' THEN 'SA' ELSE 'Unknown' END Pattern2
FROM
  Customers CONNECTION(
    Provider = 'OData',
    ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
  )
/*--------- SELECT INTO / Multiple Statements ---------*/
SELECT
  * INTO #tmpCustomers FROM Customers
  --WITH (MyParam1='something1', MyParam2='something2')
  CONNECTION(
    Provider = 'OData',
    ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
  );
  
SELECT
  * INTO #tmpOrders FROM Orders
  --WITH (MyParam1='something1', MyParam2='something2')
  CONNECTION(
    Provider = 'OData',
    ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
  );--JOIN 2 temporary tables

select
  c.CompanyName,
  Count(*) TotalOrders
FROM
  #tmpCustomers c
  INNER JOIN #tmpOrders o ON o.CustomerID=c.CustomerID
WHERE
  o.OrderDate <= DATETIME('1997-12-31 23:12:59') --date format must be yyyy-MM-dd or yyyy-MM-dd HH:mm:ss[.fff]
GROUP BY
  c.CompanyName;
/*--------- UNION ALL / UNION Statement ---------*/
SELECT
  * into #tmpCustomersUK FROM Customers Where Country='UK'
  --WITH (MyParam1='something1', MyParam2='something2');
  
SELECT
  * into #tmpCustomersUSA FROM Customers WHERE Country='USA'
  --WITH (MyParam1='something1', MyParam2='something2');

select
  *
from
  #tmpCustomersUK
UNION ALL
select
  *
from
  #tmpCustomersUSA;

Configure API Driver

Choose from Embedded Connector List
ODBC API Driver - Choose API Connector from List

Search Online Connectors and Dwonload it
ODBC API Driver - Search API Connector Online

You can create custom SQL with user defined parameters by clicking on Query Builder like below.
ZappySys API Driver - Query Builder - Generate SQL

Consume Data in your ODBC Compliant App

Here is an example of loading API data in ODBC Compliant app like Excel. Similar options are available in Informatica, Power BI etc.

ZappySys ODBC Driver : Load Data Into MS-Excel - Select ODBC Source

ZappySys ODBC Driver - Create Credentials

 

ZappySys ODBC Driver : Load Data Into MS-Excel - Success

 

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 Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

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

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

Articles


Click here to see all articles for [JSON File / REST API Driver] category


Read data from QuickBooks Online into SQL Server via ODBC Driver

Read data from QuickBooks Online into SQL Server via JSON Driver

Introduction QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement QuickBooks Online API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using ODBC in few clicks. We will use ODBC JSON / REST API Driver to read data from QuickBooks Online and Load into SQL Server / other targets (Using OAuth […]

 


Read / Write REST API data in Talend (JSON / XML / SOAP)

Read / Write REST API data in Talend (JSON / XML / SOAP)

Introduction In this post we will learn how to read / write REST API data in Talend Open Studio. We will create a simple Talend Job using ZappySys JSON  Driver to read from REST API / JSON Files and load into Target (e.g. File / DB). Techniques listed in this article can be also used to read […]

 


Insert, Update and Delete JIRA Issue in SQL Server

Insert, Update and Delete JIRA Issue in SQL Server

Introduction In our previous blog we saw how to read JIRA data in SQL Server. In this blog, we will learn how to Insert, Update and Delete Issue in JIRA using ZappySys JSON Driver. We will also read data from SQL Server data table in stored-procedure and insert JIRA issues in bulk, along with few other […]

 


Read JIRA data in SSRS Reports (SQL Server Reporting Services)

Read JIRA data in SSRS Reports (SQL Server Reporting Services)

Introduction In our previous blog we saw how to read rest api in SSRS reports. We recommend you go through that article first. In this post we will look at specific example on How to read JIRA data in SSRS Reports (SQL Server Reporting Services), along with few other topics such as how to generate an […]

 


How to Import JIRA data in Power BI

How to Import JIRA data in Power BI using JSON Driver 

Introduction In our previous blog we saw how to import rest api in Power BI. We recommend you go through that article first. In this article, we will learn how to Import JIRA data in Power BI. For that, we will create a successful HTML Connection then request for reading JIRA data and loading into Power […]

 


Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Introduction In this article, we will get Office 365  data in Power BI using Microsoft Graph API and ODBC drivers. Specifically, we will get Excel file data in OneDrive, events from a Calendar and finally, a list in a note in OneNote and then load that data into a Power BI report. We will achieve […]

 


Push data into a Power BI dataset from SQL Server

Push data into a Power BI dataset from SQL Server using JSON Driver

Introduction In this article, you will learn how to push data into a Power BI dataset from SQL Server. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Among its dataset types, it has a streaming dataset into which […]

 


Load JIRA data into SQL Server with ODBC – Call REST API

Load JIRA data into SQL Server with REST API Driver

Introduction In this blog, we will learn How to Load JIRA data in ODBC and load JIRA data into SQL Server Table, along with few other topics such as how to generate an API Token using Atlassian Account for JIRA REST API Call, how to read all Issues data from JIRA with ODBC. We will go through […]

 


Import REST API in Tableau – Read JSON, SOAP XML, CSV

Import REST API in Tableau – Read JSON, SOAP XML, CSV

Introduction Tableau is one of the most popular Reporting / Visualization tool for BI / Data analytics. It comes with many out-of the box connectors to pull data from some popular data sources but still it seriously lakes capability to consume data from millions of other REST / SOAP data sources out there for which […]

 


How to call REST API in MicroStrategy (JSON / XML SOAP)

How to call REST API in MicroStrategy using JSON Driver / XML Driver (SOAP)

 Introduction In this new article, we will show how to call REST API in MicroStrategy. MicroStrategy is a Business Intelligence company that provides powerful software to create nice charts, reports to take decisions. It started in 1993 and now it is one of the most popular Business Intelligence tools in the world because it is simple, intuitive and friendly. […]