Introduction

Content

Step-By-Step

After you install ZappySys ODBC Drivers you can use Driver functionality few ways (see below).

Using ODBC DSN (User level DSN or System level DSN)
Using Direct ODBC Connection String
Connect client app/server from Windows, Linux, Mac OS to ZappySys Data Gateway using Microsoft SQL Server Driver (i.e. JDBC, ODBC, OLEDB ...)

Regardless which method you choose from your App below steps will help you to configure your connectivity in correct way.
If you choose to connect using direct ConnectionString then also try below Steps to create correct ConnectionString which you can copy from DSN Config Tool and paste inside your App / Programming Language Code.

Using ODBC DSN (User level DSN or System level DSN)

Configure DSN

In this section you will learn how to Configure ODBC DSN. Later on this DSN can be used to use ZappySys Driver. ODBC DSN can be stored at Machine Level (for all users) or Current User Level. In this case we are going to load data into Microsoft Excel from Azure Blob Storage JSON(In this case its from Zip) file using ZappySys Azure Blob JSON ODBC Driver.

Firstly, you need following Prerequisite.
Type odbcad32.exe in your search box and launch the DSN Config utility.
If you want access for yourself then stay of User DSN Tab. If you want grant access other users then go to System DSN tab. For SQL Server Integration go to System Tab and add new System DSN rather than User DSN. Click New button.
From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys Azure Blob JSON Driver].
Now, we need Azure Blob Storage Connection. Lets create it.
Now, When you see DSN Config Editor with zappysys logo first thing you need to do is change default DSN Name at the top and Select your Blob and file from it.
Here, in the Compression (Zip/GZip) tab set suitable file format.
If you are using Direct ConnectionString method in other tool/code then just Click [Copy ConnectionString] button and hit cancel so we don't create DSN.
If you are using DSN method then Click OK to save our DSN Configuration.
Lets configure in Various Mode.
1. --- Using Direct Query ---
  Click on Preview Tab, Select Table from Tables Dropdown and select [value] and click Preview.
2. --- Using Stored Procedure ---
```
Note : For this you have to Save ODBC Driver configuration and then again reopen to configure same driver. For more information click here.
```
  Click on the Custom Objects Tab, Click on Add button and select Add Procedure and Enter an appropriate name and Click on OK button to create.
  1. --- Without Parameters ---
    Now Stored Procedure can be created with or without parameters (see example below). If you use parameters then Set default value otherwise it may fail to compilation)
  2. --- With Parameters ---
```
Note : Here you can use Placeholder with Paramters in Stored Procedure.
Example : SELECT * FROM $ WHERE OrderID = '<@OrderID, FUN_TRIM>' or CustId = '<@CustId>' and Total >= '<@Total>'
```
3. --- Using Virtual Table ---
```
Note : For this you have to Save ODBC Driver configuration and then again reopen to configure same driver. For more information click here.
```
  ZappySys APi Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.
  
  Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 Blobs with slight variations you can create virtual tables with just URL as Parameter setting).
```
vt__Customers
DataPath=myBlob_1/customers.json

vt__Orders
DataPath=myBlob_2/orders.json

vt__Products
DataPath=myBlob_3/products.json
```
  1. Click on the Custom Objects Tab, Click on Add button and select Add Table and Enter an appropriate name and Click on OK button to create.
  2. Once you see Query Builder Window on screen Configure it.
  3. Click on Preview Tab, Select Virtual Table(prefix with vt__) from Tables Dropdown or write SQL query with Virtual Table name and click Preview.
Now, lets load data into Excel Sheet Using ODBC DSN.
Open Microsoft Excel, In the Data Menu, select New Query, select From Other Sources and select From ODBC.
Now, select DSN we have created, you can write SQL Statement if you want, otherwise leave it blank for through select Table.
Click on OK button.
Now, in the Windows Tab, select an appropriate credentials and click on Connect button.
Now, Select Table you want to load data into Microsoft Excel.
Select New Worksheet for the data should be loaded.
Click on Load button to load data.
You can verify your data.

Articles

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

Get data from Google Search Console API in SSIS and ODBC Apps

Introduction In our previous few posts we saw how to call various google apis in SSIS.Â In this post lets learn how to call Google Search Console API in SSIS or other ODBC Compatible Apps such as Power BI, Informatica, SSRS using API Drivers for ODBC About Google Search Console API (Google Webmaster API) If […]

How to Make PayPal API Call in ODBC / SQL Server (T-SQL)

Introduction In our previous article we saw how to call PayPal API in SSIS. Now in this article, we will see How to Make PayPal API Call in ODBC from the SQL Server.Â This blog mainly focuses on ODBC approach but steps mentioned to call PayPal APIs can be useful for any developer regardless of which […]

Pivot JSON and XML data using SSIS or ODBC Drivers

Introduction In our previous post we saw various ways to transform JSON arrays. However there will be a time when your JSON / XML file wont have Array and you need to Pivot JSON Data. Sample JSON data file Here is a sample JSON file which we like to parse into rows and columns. Notice […]

How to Export REST API to MS Access using VBA Command Button

Introduction In our previous blog we saw how to export REST API to CSV using c# or Python. In this post we will look at specific example on How to Export REST API to MS Access using VBA Command Button, along with few other topics such as how to make REST API Call, how to […]

Read WordPress data using SSIS REST API Call

Introduction In this post you will learn how to Read WordPress data using SSIS REST API Call (Drag and drop approach without any coding).Â We will useÂ SSIS JSON/ REST API SourceÂ to extract data from WordPress API. In this blog we will see how to get all WordPress Posts, Pages and Categories using JSON Source and […]

Read data from QuickBooks Online into SQL Server via ODBC 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

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

Introduction

Content

Video Tutorial

Step-By-Step

Using ODBC DSN (User level DSN or System level DSN)

Configure DSN

--- Using Direct Query ---

--- Using Stored Procedure ---

--- Without Parameters ---

--- With Parameters ---

--- Using Virtual Table ---

Access Driver inside SQL Server (T-SQL Code)

Access Driver inside Other Tools / Programming Languages