- 1 Introduction
- 2 Prerequisites
- 3 Create Shopify Private App for REST API Calls
- 4 Read Shopify data in SSIS (Get Orders Example)
- 5 Write Shopify data in SSIS REST API Task
- 6 Create Multiple Shopify records using Web API Destination
- 7 Delete Shopify records using REST API in SSIS
- 8 Conclusion
- 9 References
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.
PrerequisitesBefore we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- 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.
- First, in Shopify, go to Apps and click Manage private apps.
- Secondly, In Private apps, click the Create a new private app:
- Also, change orders, transactions, and fulfilments permissions to read and write if you need to create, update and delete products.
- In addition, change the products, variants, and collections to read and write if you need to create, update and delete products.
- Finally, you will have an API key, a password, an URL and a shared secret that you will use in the next section:
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.
- First, in an SSIS project, drag and drop the Data Flow and double click it.
- Secondly, in the Data Flow, drag and drop the ZS JSON Source. If you are not familiar with ZS JSON Source, check this link.
- Also, press the Select Filter button and press the orders node:
- Now go to Pagination Tab and select RFC5988 Pagination mode as below. Check this link for more info on Shopify Paginated API calls.
- 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:
Load Shopify data into SQL Server / Other Target
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
- Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings
- Click OK to Save Upsert Destination Settings
- 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
- 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.
- First, use the REST API task in SSDT.
- 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:
- 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.
- Now you can click on Test Request / Response and you will see one sample order created in your admin portal.
- 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.
Delete Shopify records using REST API in SSIS
- First, in SSDT, drag and drop the ZS REST API task.
- Secondly, add the HTTP Url Connection. Use the DELETE HTTP Request Method and the URL including the order id as below:
- Now click Test Request / Response to test delete API call.
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.
Finally, for more information, refer to these links: