- 1 Introduction
- 2 Requirements
- 3 API Connector File – The Concept
- 4 Basic Example of API Connector File
- 5 Using API Connector File in Apps
- 6 Create / Edit Connector file using Wizard from API Driver UI
- 7 Create Connector file from SSIS JSON / XML Source UI Settings
- 8 Create API Connector file from scratch
- 9 Contact Us – We will build Custom API Driver for you
- 10 Conclusion
Imagine this situation, you or users of your product wants to consume some REST API in ETL / BI / Programming Tools using ODBC / JDBC Driver interface but there are no ODBC / JDBC Driver available for that API and you dont even know coding? If you are in a situation No Problem !!! In this post you will learn how to create a custom ODBC Driver for API (i.e. REST / SOAP / OData) without writing code… and access in most BI / ETL / Reporting tools using Industry Standard such as ODBC Interface….. Yes you heard it right… 🙂
Generally speaking, creating an ODBC Driver requires a lot of coding effort and that’s why most vendors don’t invest in providing ODBC Driver and let you consume their API using code only. They assume that your company / team has really sharp C# / JAVA / C++ coders available 🙂 …. but we all know that coding is not cheap and not easy to maintain in long run.
Basically, the approach mentioned in this article requires you to create an XML File (or call it Connector File) and then consume it using API Driver (ODBC) So let’s get started!
RequirementsIn order to access API data inside your App using ODBC Driver you will need to make sure following requirements are met.
- Download and Install ZappySys ODBC PowerPack (This includes XML / JSON / REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)
API Connector File – The Concept
ZappySys has developed a highly flexible API Connector Framework which you can use to build API Connector Files which can be used by API ODBC Driver in different apps. This API Connector Framework is so flexible that it can handle pretty much any API out there.
Here are a few example connector files we built using the same framework. Don’t worry about various parts in those connector files for now, you might not need to implement all those. We will start simple and explain to you advanced use cases later.
Example Connector Files
Basic Example of API Connector File
Here is a very simple example of some API to read Customers and Orders. Keep in mind most features are omitted for simplicity.
Below connector file shows few concepts which we will discuss later in this article. Such as API Base URL (i.e. Service URL), Authetication, Pagination, EndPoints, Metadata, Parameters, Templates, Tables so on.
- Open Notepad
- Copy / Paste below XMl in notepad and save as NorthwindConnector.xml
- Then you can Create a new ODBC DSN as mentioned here and use our connector file for Testing (Use Saved Connector File Option on new DSN wizard).
<ApiConfig Name="Northwind" Slug="northwind-api" Desc="Sample connector for Northwind OData Service" Logo="https://www.odata.org/assets/ODataLogo-96.png">
<ServiceUrl Name="V3_API" Url="https://services.odata.org/V3/Northwind/Northwind.svc"/>
<ServiceUrl Name="V4_API" Url="https://services.odata.org/V4/Northwind/Northwind.svc"/>
<!-- Implement supported way to Authenticate API calls -->
<Auth Name="ApiKeyAuth" Type="Http" TestEndPoint="get_customers" ConnStr="CredentialType=Token;TokenAuthHeader=X-APIKEY;Password=[$ApiKey$]">
<Param Name="ApiKey" Label="Enter your API Key" Secret="True"/>
<!-- Reusable Templates (i.e. API Pagination Properties) -->
<Param Name="NextUrlAttributeOrExpr" Type="Property" Value="$.['odata.nextLink']"/>
<!-- EndPoints -->
<EndPoint Name="get_customers" Template="PaginatedCall" Url="/Customers?$format=json" Filter="$.value[*]" Method="GET">
<Column Name="CustomerID" Label="Id" DataType="DT_WSTR" Length="20"/>
<Column Name="CompanyName" DataType="DT_WSTR" Length="100"/>
<EndPoint Name="get_orders" Template="PaginatedCall" Url="/Orders?$format=json" Filter="$.value[*]" Method="GET">
<Column Name="OrderID" Label="Id" DataType="DT_I4"/>
<Column Name="OrderDate" DataType="DT_DBTIMESTAMP"/>
<Column Name="CustomerID" DataType="DT_WSTR" Length="20"/>
<Column Name="Notes" DataType="DT_NTEXT"/>
<!-- Tables (with SELECT, LOOKUP UPDATE, INSERT, DELETE operations. For Demo we have used just SELECT EndPoint) -->
<Table Name="Orders" SelectEndPoint="get_orders"/>
<Table Name="Customers" SelectEndPoint="get_customers"/>
Using API Connector File in Apps
Once API connector file is created
Use in ODBC Apps
Click here to see an example – Using API connector file in ODBC Apps (ODATA). For other popular apps check this page and click on desired app.
Here is how it may look like if we import the previous connector file in API Driver UI. Notice how we selected the V3 Base URL as API. Also, see how various connector file elements translated to Driver UI…. very cool right ?
Here is the output from Preview Tab
Use in JAVA Apps
Click here to see an example – Using API connector file in JAVA based Apps (It uses ZappySys Data Gateway as Bridge rather than direct ODBC call in Java becuase JAVA doesnt support ODBC directly)
Use in SQL Server (T-SQL code)
Click here to see an example – Using API connector file in SQL Server (It uses ZappySys Data Gateway as Bridge rather than direct ODBC call in SQL Servr becuase of some known issues of direct ODBC)
Use in SSIS Package
Click here to see an example – Using API connector file in SSIS Packages. It uses native API Source / API Destination components.
Create / Edit Connector file using Wizard from API Driver UI
If you like to edit your file previously created using Wizard then you can do the following steps.
- Assuming you have already setup ODBC DSN for API Connector File we previously created.
- Open Odbc UI (search for “ODBC” in the start menu and select “ODBC Administrator”)
- Double-click on the Data source you like to edit
- On the Properties Tab > Go to Advanced Tab
- Click on Customize – Current Connector File (If you wish to create a new API connector file then you can click on Create New Connector File Button instead)
Create Connector file from SSIS JSON / XML Source UI Settings
If you are an SSIS user and you have configured JSON Source or XML Source then you convert those UI settings into a basic API connector file by going on API Template and clicking on Create New Connector File button like below.
Create API Connector file from scratch
So far you have understood the basic ideas about connector files. Also explored creating / editing API Connector File using Wizard in ODBC / SSIS.
Now let’s look at concepts of creating connector file in detail.
Basic Properties (Name, Slug, Logo)
Service URL (API Base URL)
Metadata / OutputColumns
Contact Us – We will build Custom API Driver for you
If you are still not sure how to use all these on your own or you do not have time/experience… no worries. Contact us and we will provide you an estimate for building a custom API Driver / connector.
In the contact form above provide us following details (or as much as you can provide)
- Which connector do you like to build?
- How are you planning to consume that data (i.e. which apps e.g. Power BI, Tableau)?
- Your use case (e.g. read customer data from Zoho Invoice System)
- Include table / data feed names you prefer to read from (e.g. Read Invoices, Read/Write Customers)
In this article, we explored how easy it is to create your custom ODBC Driver for API using a powerful API Connector Framework. Download ODBC PowerPack and build your own API Driver in a few clicks.