How to List Tables (JSON API) — OData Connector for SSIS

Prerequisites

Before we begin, make sure the following prerequisites are met:

SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
SSIS PowerPack is installed (if you are new to SSIS PowerPack, then get started!).

List Tables (JSON API) in SSIS

Begin with opening Visual Studio and Create a New Project.
Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.

In the new SSIS project screen you will find the following:
- SSIS ToolBox on left side bar
- Solution Explorer and Property Window on right bar
- Control flow, data flow, event Handlers, Package Explorer in tab windows
- Connection Manager Window in the bottom
Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link.
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
Select New Connection to create a new connection:
Use a preinstalled OData Connector from Popular Connector List or press Search Online radio button to download OData Connector. Once downloaded simply use it in the configuration:

OData

Now it's time to configure authentication. Firstly, configure authentication settings in OData service and then proceed by configuring API Connection Manager. Start by expanding an authentication type:

No Authentication

OData authentication

No instructions available.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to No Authentication [Http]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

OData

No Authentication [Http]

https://services.odata.org/V3/Northwind/Northwind.svc

Optional Parameters
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429
RetryCountMax	5
RetryMultiplyWaitTime	True
IgnoreSSLCertificateErrors
SslVersion

Windows Authentication

OData authentication

No instructions available.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to Windows Authentication [Http]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

OData

Windows Authentication [Http]

https://services.odata.org/V3/Northwind/Northwind.svc

There are no parameters to configure.

Basic Authentication

OData authentication

No instructions available.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to Basic Authentication [Http]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

OData

Basic Authentication [Http]

https://services.odata.org/V3/Northwind/Northwind.svc

Required Parameters
UserName	Fill-in the parameter...
Password	Fill-in the parameter...
Optional Parameters
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429
RetryCountMax	5
RetryMultiplyWaitTime	True
IgnoreSSLCertificateErrors
SslVersion

NTLM Authentication

OData authentication

No instructions available.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to NTLM Authentication [Http]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

OData

NTLM Authentication [Http]

https://services.odata.org/V3/Northwind/Northwind.svc

Required Parameters
UserName (i.e. Domain\UserName)	Fill-in the parameter...
Password	Fill-in the parameter...
Optional Parameters
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429
RetryCountMax	5
RetryMultiplyWaitTime	True
IgnoreSSLCertificateErrors
SslVersion

Token Authentication

OData authentication

No instructions available.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to Token Authentication [Http]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Finally, hit OK button:

OData

Token Authentication [Http]

https://services.odata.org/V3/Northwind/Northwind.svc

Required Parameters
Token	Fill-in the parameter...
Optional Parameters
AuthScheme	Bearer
AuthHeader	Authorization
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429
RetryCountMax	5
RetryMultiplyWaitTime	True
IgnoreSSLCertificateErrors
SslVersion

OAuth Connection for OData API

OData authentication

No instructions available.

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

Set Authentication Type to OAuth Connection for OData API [OAuth]
Optional step. Modify API Base URL if needed (in most cases default will work).
Fill in all the required parameters and set optional parameters if needed.
Press Generate Token button to generate the tokens.
Finally, hit OK button:

OData

OAuth Connection for OData API [OAuth]

https://services.odata.org/V3/Northwind/Northwind.svc

Optional Parameters
GrantType	Default
OAuthVersion	Default
ClientId
ClientSecret
UserName
Password
Scope
AuthUrl	https://some-domain.com/api/auth
TokenUrl	https://some-domain.com/api/token
ReturnUrl	https://zappysys.com/oauth
ExtraAttributesForTokenRequest
ExtraAttributesForAuthRequest
DoNotIncludeCredentialsInHeader
DoNotIncludeCredentialsInBody
RenewBeforeSec
ExtraHeaders (e.g. x-hdr1:AAA\|\|x-hdr2:BBB)
ContentType
EnableCodeVerifier
RemoveAuthHeader
CustomAuthHeader
CustomTokenPrefix
RetryMode	RetryWhenStatusCodeMatch
RetryStatusCodeList	429
RetryCountMax	5
RetryMultiplyWaitTime	True
IgnoreSSLCertificateErrors
SslVersion

Select List Tables (JSON API) endpoint from the dropdown and hit Preview Data:

API Source - OData

OData Connector can be used to integrated any REST API which supports OData Standard. You can view tables, read data from tables using this connector. It also comes with some example OData Services to get started.

OData

List Tables (JSON API)

There are no parameters to configure.
That's it! We are done! Just in a few clicks we configured the call to OData using OData Connector.

You can load the source data into your desired destination using the Upsert Destination , which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV , Excel , Azure Table , Salesforce , and more . You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)