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

See also JSON File / REST API Driver XML File / SOAP API Driver CSV File / REST API Driver Data Gateway

Download Help File Buy

View All Drivers

Prebuilt Connector Files

Featured Articles

• Import JSON to Excel (Load File, REST API, SOAP XML)
• How to Import REST API in Power BI (Load JSON / SOAP XML)
• Import REST API in SQL Server (Call JSON / XML SOAP Service)
• Call REST API in Python (Read JSON / SOAP XML)
• How to read REST API in SSRS Reports (JSON / XML / Call SOAP Web Service)
• Read JSON in Informatica – Import REST API / SOAP / JSON File
• Read Amazon S3 data in Power BI – Call AWS API (JSON / XML)
• How to Import LinkedIn data in Power BI
• Calling REST API in C# (Read JSON Data)
• How to call Salesforce REST API via ODBC driver

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

Search Online Connectors and Dwonload it

You can create custom SQL with user defined parameters by clicking on Query Builder like below.

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.

Articles

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

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) 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 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) 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 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 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 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 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 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 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. […]