Manual for REST API in SSIS

Introduction

There are several scenarios and configurations in REST API. Facebook, Linkedin, YouTube, Amazon use several types of connections and it is difficult for users to connect the first time. You can connect to REST API using OAuth1, OAuth2, HTTP with basic authentication, dynamic tokens, base64 encryption. There are thousands or different REST API connections and our customers are sometimes lost with so many connections. That is why we decided to create a guide for different configurations and connections. Here you have a list of links of different configurations.

1. How to connect SSIS to REST API using OAuth1
2. How to connect SSIS to REST API using OAuth2
3. How to connect SSIS to REST API using OAuth2, grant type
4. How to connect SSIS to JWT
5. How to convert curl to SSIS REST API calls
6. How to connect SSIS to REST API using  basic authentication
7. How to connect SSIS to REST API using a dynamic token
8. How to connect SSIS to REST API using HMAC Authentication
9. How to connect SSIS to REST API using Pagination
10. How to solve the SSIS REST API Error 400 Bad request error, REST API Error 401 Unauthorized error, REST API Error 404 Page not found, REST API Error 405 Method is not allowed, REST API error 415 Media Type is not supported
11. How to debug SSIS REST API request
12. How to connect SSIS to REST API to upload files

What is OAuth?

It is a secure protocol to access information from the website. There are two versions, OAuth 1.0 and OAuth 2.0. This mecanism for access delegation is used by many companies like Facebook, Youtube, Twitter, Gmail, etc.

How to connect SSIS to REST API using OAuth1

OAuth 1 was the first standard and it was complex than OAuth 2.0 the OAuth 1.0 is not compatible with OAuth 2.0. OAuth 1.0 was created in 2010, so it is more common to use OAuth 2.0. Twitter started using OAuth 1.0 in august of 2010.

The following article shows how to connect using OAuth 1.0:

• Call Semantics3 REST API in SSIS use OAuth1

How to connect SSIS to REST API using OAuth2

Most of the Web Services connect to the clients using OAuth 2.0. This protocol is simpler,

The following article shows how to connect SSIS to REST API using OAuth 2.0.

• How to authenticate to an API with OAuth 2.0 using SSIS / ODBC

How to connect SSIS to REST API using OAuth2, grant type

Grant type is one of the options avaialbe when OAuth 2.0 is selected. This option generates a token each time that the connection is made. For more information about Grant Type in SSIS, refer to this link:

• How to call API in SSIS with OAuth 2.0 Client Credentials Grant Type

How to connect SSIS to REST API using OAuth2, grant type

Grant type is one of the options avaialbe when OAuth 2.0 is selected. This option generates a token each time that the connection is made. For more information about Grant Type in SSIS, refer to this link:

• How to call API in SSIS with OAuth 2.0 Client Credentials Grant Type

How to connect SSIS to JWT

JWT (JSON Web Token) is a JSON standard used to create tokens. Zappysys also can connect to JWT. For more informaton about JWT and SSIS, refer to this link:

• How to call Zoom REST API using SSIS (OAuth / JWT)

How to convert curl to SSIS REST API calls

cURL is a project with a library and a command line to invoive REST API. For REST API calls it is common to use curl and many customers ask us how to convert CURL calls to SSIS REST API calls. The following article will provide information about converting cURL to SSIS REST API:

• Convert CURL Command Line to SSIS API Task

How to connect to REST API using SSIS basic authentication

If your REST API requires basic authentication, you will need to create an ZS HTTP connection and specify a login and password. The following article shows an example about REST API with basic authentication:

• Calling REST API in SSIS with Basic Authentication / Userid-Password (BASE64 Authorization header)

How to connect to REST API using SSIS with a dynamic token

Another way to connect is the dynamic token. It requires a login and then a token is generated. You need to use the HTTP connection for this connection. For more information, refer to this link:

• How to call SOAP / REST API using Dynamic Token in SSIS

How to connect SSIS to REST API using HMAC Authentication

HMAC is a hash-based message authentication code. Usually the cryptographic function to hash is MD5, SHA256 or SHA3.

For more information about HMAC Authentication, refer to this link:

• Using HMAC Authentication for API in SSIS / ODBC Drivers

How to connect SSIS to REST API using Pagination

Pagination is a method to handle large datasets. There are several ways to handle Pagination. The following article shows the most common ways using SSIS and ZappySys:

• Understanding REST API Pagination in SSIS / ODBC Drivers

How to solve the SSIS REST API Error 400 Bad request error, REST API Error 401 Unauthorized error, REST API Error 404 Page not found, REST API Error 405 Method is not allowed, REST API error 415 Media Type is not supported

These problems may be related to wrong parameters in the URL or a Wrong body request. wrong URL, wrong body. For more information about these typical errors, refer to this link:

• Understand HTTP Status Code and Fix Common Errors in REST API

How to debug SSIS REST API request

The REST API debug can be done with Fiddler. This tool is external, but provide nice information for debugging. The following URL shows how to use it:

• How to use Fiddler to analyze HTTP Web Requests

How to connect SSIS to REST API to upload files

Sometimes the REST API allows to upload files. The following article shows an example about  it:

• REST API File Upload using SSIS (Multi Part POST) 

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/