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

See what people say about ZappySys…

Awesome framework to bring a small company into the world of connectivity!

Zappysys has been a fantastic way to bring our small company into the world of apis, automation, and systems integration by providing tools within an easy-to-use framework. As a small company we do not have the resources
to hire a full time developer. For our company, Zappysys has provided the essential framework needed for us to take everything to the next level by getting us past many of the nuts and bolts hurdles to initial stages of development.
Additionally, every time we have worked with their support team they have provided a one-on-one consulting session to solve our problem. And what’s more is they always answer the call quickly so we are never left hanging out to
dry.

Ben Kingery, Oxspring Paul

Great Product & Great Support

The responsiveness of your support staff during the evaluation period really has been excellent. I’ve contacted them a few times with queries specific to the scenario I am working with and they have answered straight
away via chat and provide exactly the detailed technical input required to progress. Please convey my sincere gratitude to your support team, they have been a great help. The functionality of the component that is of particular
interest (the REST source) is very powerful, and due to constraints regarding access to the environment that I am targeting, it is in fact is the only thing I have found that I am able to use and I have tried a few other components
from major vendors also.

Jes Kirkup, KTC

Very resourceful and helpful

We have been working with the support team at ZappySys for over a year now and have consistently found them to be extremely helpful in resolving any issues we have encountered. They have proven themselves to be resourceful
and creative in offering multiple solutions to overcome any and all challenges we have faced in our day-to day-operations. Either online or by telephone, their personnel are reliable, energetic and sensitive to the urgency of our
problem-solving needs. A well-organized team of real go-getters.

Senthil Sekar, Publicis Groupe

We are so glad that we chose ZappySys…

We have been using ZappySys for our data warehouse SSIS ETL for a couple of years and we LOVE the product so much. It comes with so many features and functions. Whenever we run into obstacle, ZappySys has a solution
for us. Support response is fast and get the right answer all the time. At the very beginning when we were evaluating third party SSIS controls, we also tried CData software which was fast and easy to connect to data sources and
destinations. However, features and functions were very limited partly due to data schema was pre-programmed by CData. Since every business is different, we needed something flexible and granularity. We are so glad that we chose
ZappySys PowerPack.

Kevin Chen, Berricle

When it comes to integrating data flow between multiple technologies, the ZappySys software and their amazing support are well worth the investment.

At Synchronous Customers, we need a way to connect various types of data together quickly. The super handy Zappysys Connectors and great documentation allow us to do this will having to write a lot of the code. The system
is stable and reliable and the support team really comes through for us if we get stuck on something. The tools are more than just drag and drop, point and click. They include all of what you need to see what is really going on
under the hood and tools for troubleshooting. Thank you ZappySys! We are glad we found you and highly recommend!

Joel Conner, Synchronous Customers

Great Support

I couldn’t be happier with the support I received this evening. I had been building out a proof of concept and just couldn’t get past one last error. I submitted a support request and had someone logged in and fixing
things within 2 hours. Not only did they solve my problem but went on to assist with answers to basic SSIS tasks. I enthusiastically support this product.

Barry Wilson

ZappySys is a very helpful tool

ZappySys tool is very helpful tool, and the technical staff are incredible. I have been able to accomplish my project using the ZappySys tool with the help of well-organized documentations. Thanks a lot.

Dinku, Sisay

ZappySys SSIS PowerPack is a sensational product, with a great support team. In my book, this makes it stand above the other products in the marketplace.

I looked everywhere for a product that could easily integrate to a specific web portal backend api via JSON, and after many attempts the ZappySys product was the only solution that could give me all the options I needed
in a quick & clean interface within SSIS. Coupled with some great tech support and customer support, I would definitely recommend ZappySys SSIS PowerPack.

Maz Dunic, Save the Children Australia

Read reviews on Capterra.com

Read reviews on Trustpilot.com

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.

ODBC Integration Screenshots in various tools

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 )

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)

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#
• C++
• JAVA
• Python
• PHP
• PowerShell
• T-SQL (Using Linked Server)

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

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 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

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…)

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