How to call eBay REST API in SQL Server with SSIS

 Introduction

eBay recently launched brand new REST API with OAuth 2.0 support. This article will explain you how to call eBay REST API using SSIS to automate eBay operations (e.g. Buy/Sell) using SSIS REST API Integration Connectors / OAuth Connection Manager.

This article assumes that you have basic knowledge of SSIS (SQL Server Integration Services). We will use drag and drop approach without coding to do eBay REST API integration. We will use following custom tasks/connectors from SSIS PowerPack

SSIS PowerPack REST API Connectors can be used to consume/manage eBay data using API calls. Following connectors will be used in this article.

Register eBay OAuth App

Before you can call any eBay REST API you have to register OAuth App. Lets look at high level steps needed to register eBay OAuth app and get credentials to make REST API Call later in this article.

  1. To call eBay REST API you will have to create eBay Developer account. Its free.
  2. Once you login to your developer portal register ebay OAuth App for Sandbox/Production. eBay supports Sandbox and Production API so we suggest you to create Apps for both API types (i.e. create key sets).

    Best practice is to test everything first using Sandbox API and then change URLs and keys to Production API when you ready to switch. To learn more about eBay API Testing in Sandbox Click here

  3. Once you register eBay OAuth App (Production or Sandbox) you will need following information for our SSIS OAuth Connection (Used later in this article)
    1. App ID (i.e. Client ID)
    2. Cert ID (i.e. Client Secret)
    3. RuName (i.e. Redirect URL). Click here to learn how to get RuName (scroll to the section which says Getting the RuName value in that link). RuName is specified in Redirect URL field on SSIS OAuth connection manager (on Advanced Tab). When you create Redirect URL you will need to specify https:// in Accept URL field as below screenshot. If you are not sure of if you don’t have server which can accept https:// redirect call then enter below URL.
      https://zappysys.com/oauth/
  4. Once OAuth App created you will need atleast one test user account to test Sandbox API (you cannot use Developer account used for App registration). You can create test user for sandbox here
  5. Once you have above steps done you are ready for your very first eBay REST API call from SSIS 🙂
  6. Any API calls described on this article can be tested using Official eBay API Test tool here.

Test Ebay API using Online API Testing Tool

Before you can dig deep down we suggest you to try few API calls using ebay online testing tool. Here is an example api call using their Online Testing Tool.

For example visit below URL to try sample Search API call.

Testing Ebay REST API using Online Tool

Testing Ebay REST API using Online Tool

 

Call eBay API using SSIS – Create new inventory item

Now lets look at how to make simple eBay REST API call using SSIS. For demo purpose we will use Sandbox API rather than production.

  1. First download and install SSIS PowerPack from here.
  2. Create new SSIS Project and open SSIS Package
  3. Drag new ZS REST API Task from SSIS Toolbox on control flow designer
  4. Rename ZS REST API Task to Create eBay inventory item-1. Double click the task to configure it.
  5. Select Request mode to [Url from Connection]
  6. Select ZS-OAUTH connection type from connection dropdown.
  7. On OAuth connection. Select eBay Sandbox from provider dropdown.
  8. Enter App ID in Client ID field (Obtained from here)
  9. Enter Cert ID in Client Secret field  (Obtained from here)
  10. Select desired Scopes or leave it blank to use default scopes. For this example we need following two scopes
    https://api.ebay.com/oauth/api_scope/sell.inventory
    https://api.ebay.com/oauth/api_scope/sell.inventory.readonly
  11. Click on Advanced Tab. Enter RuName in the Return URL field. Don’t worry if your RuName doesn’t look like URL 🙂 (Click here to learn how to get RuName – Scroll to Getting the RuName value)
  12. Now click on first tab again. Click Generate token button. When prompted login using Sandbox User Account (Click here to create test user for Sandbox).
    NOTE: Production account or developer account wont work when you login for eBay Sandbox API Token
  13. Click Accept button after login. If everything goes well you will see two tokens populated on your main screen (Access Token and Refresh Token). You will get prompt to save tokens to some backup file… Do that so same tokens can be used if you wish to deploy JOB to another machine.
  14. Click Test to validate connection. Click OK to save.
    SSIS OAuth Connection Manager - Connect to eBay Sandbox API

    SSIS OAuth Connection Manager – Connect to eBay Sandbox API

  15. Now once you close connection you are back to REST API Task
  16. Enter following URL to create or new eBay inventory item using PUT. Last part of following URL is ID we want to create
  17. Select Method PUT from dropdown
  18. Click Edit for Request Body. Enter following data for our sample product
  19. Change Body content type to JSON from dropdown
  20. Click on [Raw Edit] button in Headers grid toolbar. Enter following headers and click OK.
     
  21. Click Test Request/Response – If you get success that means product got created.SSIS Rest API Task - Call eBay REST API to create new inventory item
  22. You can create more products by simply changing SKU in URL and name/description in Body. Click Test to execute API call.

Read data from eBay and load into SQL Server using SSIS JSON Source

Now lets look at how to consume eBay REST API and load data into SQL Server. For this you can use JSON Source Connector (File, REST API). For this demo we will Call eBay Search API . If you want to explore other API then check this page. Here is

  1. Go to Data flow tab
  2. Drag and drop ZS JSON Source from SSIS Toolbox
  3. Double click to configure it.
  4. Enter API URL like below. We added Search parameter and limit parameter (Max rows per response). Refer to API Help page to know more about other available parameters.
  5. Check Use Credentials option.
  6. Select ZS-OAUTH connection or Create New (See previous section to create new one)
  7. Select Filter (Look for Array Icon and select that node). For example if you calling search API then  enter or select  $.itemSummaries[*]  like below.
    SSIS JSON Source Connector - Get data from eBay REST API (Use OAuth Connection)

    SSIS JSON Source Connector – Get data from eBay REST API (Use OAuth Connection)

  8. Now click on pagination tab and enter $.next  expression for next link attribute as below. This will enable pagination (It will keep reading in 100 rows per page until last page reached – Empty  recordset)
    Ebay REST API Pagination (Search API Example)

    Ebay REST API Pagination (Search API Example)

  9. That’s it, now click Preview to see data
  10. Once all looking good you can click OK to save JSON Source.
  11. In Next Section we will see how to load Ebay Data into SQL Server.

Loading eBay data into SQL Server

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).
  1. Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
  2. Connect our Source component to Upsert Destination
  3. Double click Upsert Destination to configure it
  4. Select Target Connection or click NEW to create new connectionConfigure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings SSIS Upsert Destination - Columns Mappings
  7. Click OK to Save Upsert Destination Settings
  8. That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
  9. To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
 

Pagination for eBay REST API Calls

In previous example we saw REST API Pagination using Next Link approach but some API may not use same approach in that case refer to this link.  We looked at Next Link approach in previous section, you can also use Method #2 (URL parameter  – Offset) but Next Link approach is more simpler.

Calling eBay XML format API – Finding / Trading / Shopping API

eBay converted most of their APIs to JSON format to use OAuth 2.0 standard but there few APIs still there which are not OAuth 2.0 standard. These includes eBay Trading API, eBay Finding API, eBay Shopping API. They all are still in XML format. Below section describe how to call these API. Use eBay API Test tool here to generate desired Request Body and Headers.

Now let’s look at an example to call eBay XML API (GetItem from Trading API). To support special case like eBay API ZappySys introduced OAuth connection property called CustomAuthHeader in v2.6.7 or higher (Only found in Properties grid view).

  1. Right click on OAuth connection manager icon > Click Properties > Set CustomAuthHeader to X-EBAY-API-IAF-TOKEN
  2. Now drag ZS REST API Task from Control flow SSIS Toolbox
  3. Set Properties like below
    1. Set URL as below (Sandbox Url is different than Production)
    2. Set Body As (this was obtained from  eBay API Test tool here )
    3. Set Headers as below (Click on Raw Edit Button and paste below heades). Again these header were also copied from eBay API Test tool here . Notice that API function name is included in the header. Also site id and compatibility level required.
    4. Now click OK to get response. If things working you will see some valid XML.
      Call eBay XML API using SSIS REST API Task (eBay Trading API, Shopping API, Finding API using OAuth Token)

      Call eBay XML API using SSIS REST API Task (eBay Trading API, Shopping API, Finding API using OAuth Token)

User Token vs Application Token

Any time you call eBay API you have to supply token. eBay OAuth supports two types of token. In this article we used User Token. If you wish to use Application Token then you have to use OAuth Client Credentials grant. See this article

Difference between eBay Application Token vs User Token

  1. Application Token
    – Do not require user login
    – Use this token if you need to perform basic operations which are supported by following permission   https://api.ebay.com/oauth/api_scope
  2. User Token
    – Requires one time user login to create token and then refreshes automatically without login prompt (if you using SSIS PowerPack)
    – Use this token if you need higher privileges behalf of actual use for update/write data using API call

Use Application token when you want to perform readonly operations and you don’t need higher privileges (such as admin access). Certain eBay API calls only possible if you supply user token.

POST data to Ebay (Insert or Update)

In previous section we have seen how to read data from ebay. Now let’s look at how to write data to ebay.

There are two ways you can achieve this.

  1. Use Web API Destination in Data Flow
  2. Use REST API Task in Control Flow

Read this article to learn more about API POST using SSIS

Using SSIS Web API Task (Write data from SQL Server to ebay)

SSIS Web API Destination - POST JSON to REST API Endpoint, Create / Update records

Using REST API Task to POST data to ebay

If you have JSON/XML data already prepared and if you like to POST it to ebay API then REST API Task would be easy to use.

REST API Task Body can be direct string, variable (e.g. {{User::varSomeData}} or Body can come from File 

SSIS REST API Task - POST data to Xero (Create contacts)

SSIS REST API Task – POST data to ebay (Create contacts)

Common Errors

Truncation related error

The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected. For detailed instructions on how to fix common metadata related errors read an article "How to handle SSIS errors (truncation, metadata issues)".

Authentication related error

Another frequent error you may get is an authentication error, which happens when you deploy/copy a package to another machine and run it there. Check the paragraph below to see why it happens and how to solve this problem.

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

Check our article on how to configure packages with sensitive data on your production or development server.

Conclusion

REST API integration typically requires coding and good understanding of protocol such as OAuth. SSIS PowerPack REST API connectors and OAuth Connection provides easy to use interface with many options to integrate virtually any API including eBay REST API inside your SQL Server Environment without any coding. Try SSIS PowerPack yourself see what is possible.

Posted in REST API, REST API Integration and tagged , , , , , , , .