API ODBC Driver – For Online ServicesAPI 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
|
||||
|
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
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 – 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
IntegrationETL Tools
Integration
Programming Languages
Integration
ODBC Integration Screenshots in various tools
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.
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 – 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
IntegrationETL Tools
Integration
Programming Languages
Integration
ODBC Integration Screenshots in various tools
Articles
Click here to see all articles for [JSON File / REST API Driver] category
 |
Read data from QuickBooks Online into SQL Server via JSON DriverIntroduction 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 ServerIntroduction 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 DriverIntroduction 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 ODBCIntroduction 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 DriverIntroduction 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 DriverIntroduction 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, CSVIntroduction 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. […] |