Read / Write Shopify data in SSIS (REST API)

Introduction

In our previous article, we saw how to call REST API in SSIS. Now in let’s use that knowledge and learn how to read/write Shopify data in SSIS. If you are not aware of Shopify then its one of the most popular eCommerce platforms out there for small shops who sell online. Shopify provides a total framework to sell/fulfill orders online including tools to build/customize your own web store.

In this article we will see few examples on how to call basic REST API calls to read shopify data, delete shopify data and write shopify data using various SSIS Components.

 

 

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Create Shopify Private App for REST API Calls

Before you call shopify REST API, your first step is to obtain credentials. We will need API Key and Password. We will use it in later section to call shopify REST API.

  1. First, in Shopify,  go to Apps and click Manage private apps.
    Manager private apps in Shopify

    Shopify App – Manager private apps

  2.  Secondly, In Private apps, click the Create a new private app:
    Create a new Shopify private app

    Shopify Private apps

  3. Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.
  4. In addition, change the products, variants, and collections to read and write if you need to create, update and delete products.
    Also change to read and write permissions

    Permission for private apps.

  5. Finally, you will have an API key, a password, an URL and a shared  secret that you will use in the next section:
Shopify password

Shopify API key

 

Read Shopify data in SSIS (Get Orders Example)

So in this section we will learn how to call Shopify REST API.  For example purpose we will call Shopify Orders Endpoint. For all other REST API endpoints check this link. Read carefully which parameters you can pass for each API call. So let’s read shopify orders using sample API call.

Configure SSIS JSON Source to read from Shopify

Now, that we created an App in Shopify, the rest is easy.

  1. First, in an SSIS project, drag and drop the Data Flow and double click it.
    Drag the Data flow task

    Use the Data Flow

  2. Secondly, in the Data Flow, drag and drop the ZS JSON Source. If you are not familiar with ZS JSON Source, check this link.
    ssis shopify connection

    SSIS JSON Source HTTP connection

  3. Also, press the Select Filter button and press the orders node:
    filter in ssis to get shopify data

    SSIS Shopify filter

  4. Now go to Pagination Tab and select RFC5988 Pagination mode as below. Check this link for more info on Shopify Paginated API calls.
    Link Header Pagination Method (RFC 5988)

    Link Header Pagination Method (RFC 5988)

    NOTE:  For Older API use Cursor-based pagination as below (version 2019-07 or older). 
    SSIS JSON API pagination

    SSIS pagination for Shopify

  5. Finally, make sure to the Use Credentials option and select the http connection of the step 2 and press the Preview button to see the data:
Get Shopify data in SSIS

SSIS preview Shopify data

 

Load Shopify data into SQL Server / Other Target

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
 

Write Shopify data in SSIS REST API Task

In this new example, we will write Shopify data in SSIS. In addition, we will create a new order using REST API to write Shopify data in SSIS.

  1. First, use the REST API task in SSDT.
  2. Secondly, in the REST API use the http connection created on step 2 of the read Shopify data in SSIS.  Also, in HTTP Request Method, select POST, in Body (Request Data) write the following:
    write shopify data in ssis

    Post JSON order Shopify

     

  3. If you dont like to use URL supplied in HTTP Connection manager then just check override URL option and enter URL like below. For this example we used correct URL in HTTP connection manager so we will not override URL.
    https://{{your-store}}.myshopify.com/admin/orders.json
  4. Now you can click on Test Request / Response and you will see one sample order created in your admin portal.
  5. You can supply variables in Body by clicking on Insert Variable dropdown when you edit Body.

Create Multiple Shopify records using Web API Destination

With our ZS Web API destination, it is possible to create multiple records at the same time.  The following link shows the ZS Web API Destination information:

Here are few useful articles to explain Web API destination use case.

How to POST data to URL

Few other articles

Delete Shopify records using REST API in SSIS

  1. First, in SSDT, drag and drop the ZS REST API task.
  2. Secondly, add the HTTP Url Connection.  Use the DELETE HTTP Request Method and the URL including the order id as below:
    https://{{your-store}}.myshopify.com/admin/orders/{{orderid}}
    Shopify API in SSIS

    SSIS delete data in Shopify using REST API call

     

  3. Now click Test Request / Response to test delete API call.

Conclusion

In this article, we learned how to create a Shopify app. In addition, we learned how to connect to Shopify using SSIS with the ZappySys tools. Also, we learned to use ZappySys tools in SSIS to get, write and delete information.

References

Finally, for more information, refer to these links:

Posted in REST API Integration, SSIS JSON Source (File/REST), SSIS REST API Task, SSIS WEB API Destination and tagged , , , .