If you have connected to Shopify with SmartConnect and have more than 250 records to query (Customers, Products, Orders, etc.) in your Shopify account you have likely run into the Shopify API limit.  Shopify limits each call to 250 records and breaks them out into pages.  To handle this, we need to run a map for every 250 records using a different page and map for each run.  To start, make sure you have Shopify setup using the REST Connector in SmartConnect.  If you need assistance with this please look at this article. We also need to set up a couple global variables and a default SQL connection to the SmartConnect database.

Create two global variables.

  1. GBL_SHOPIFYPRODUCTPAGES with a blank default value.
  2. GBL_SHOPIFYPRODUCTSCURRENTPAGE with 1 as the default value.


Create a SQL Default Connection


Once you have the connector, variables, and default SQL connection setup you will need to modify the URL for the Get All Products method to take the paging into consideration.  The new URL will be: https://{Store_Prefix}.myshopify.com/admin/products.json?limit=250&page=GBL_SHOPIFYPRODUCTSCURRENTPAGE

In this method we are defining the max record count and which page we want the data from using a global variable.  We will populate the GBL_SHOPIFYPRODUCTSCURRENTPAGE in a script below.

Now that we have the connector and variables setup, we need to setup our maps and get the real page count so we know how many times to run our maps.  We need two maps to handle the paging.  The first map will handle the odd pages and the second map will handle the even pages.  The maps will alternate runs until there are no pages remaining to process.

Map one will use your Get All Products method from the REST connector as a source.  Add a Run Script Task that will run before the map.  This script will get how many pages of products are in your Shopify system.  You will need to update the Authorization to your token and update the endpoint to include your store prefix.

Dim header As New System.Net.WebHeaderCollection() ‘create header

header.Add(“Authorization: Basic YOUR_TOKEN”) ‘add your token to header

Dim sUrl As String = “https://YOUR_STORE_PREFIX.myshopify.com/admin/products/count.json” ‘endpoint to call – count of all products

Dim request As System.Net.HttpWebRequest = TryCast(System.Net.WebRequest.Create(New Uri(sUrl)), System.Net.HttpWebRequest)

request.Headers = header

request.Accept = “application/json”

request.Method = “GET”

Dim response As System.Net.WebResponse = request.GetResponse()

Dim dataStream As Stream = response.GetResponseStream()

Dim reader As New StreamReader(dataStream)

Dim responseFromServer As String = reader.ReadToEnd()

‘Parse json page response

responseFromServer = responseFromServer.Substring(9)

responseFromServer = responseFromServer.Trim(“}”.ToCharArray())

Dim totalpages as Integer

totalpages = responseFromServer/250 ‘get total pages

GBL_SHOPIFYPRODUCTPAGES = totalpages ‘set GBL_SHOPIFYPRODUCTPAGES variable to current pages

We also need to add a Map Post Task if the map succeeds.  This script will update the current page variable so the next map will process data from page two and so on as the loop continues until we run out of pages to process.

Dim connection As SqlConnection = New SqlConnection(_SQLDEF_SMARTCONNECTDB)

‘check to see if the current page is less than the total pages and increment

If cint(GBL_SHOPIFYPRODUCTSCURRENTPAGE) < cint(GBL_SHOPIFYPRODUCTPAGES) Then

GBL_SHOPIFYPRODUCTSCURRENTPAGE = GBL_SHOPIFYPRODUCTSCURRENTPAGE + 1

‘update the global variables accordingly for next map run

Using connection

connection.Open()

Dim cmd2 As SqlCommand = New SqlCommand(String.Format(” UPDATE GlobalVariable  SET Value =” & GBL_SHOPIFYPRODUCTSCURRENTPAGE & “WHERE [Name] = ‘GBL_SHOPIFYPRODUCTSCURRENTPAGE'”), connection)

cmd2.ExecuteNonQuery()

connection.Close()

End Using

return true

Else

GBL_SHOPIFYPRODUCTSCURRENTPAGE = 1 ‘current page is not greater than products page, set back to 1

Using connection

connection.Open()

Dim cmd2 As SqlCommand = New SqlCommand(String.Format(” UPDATE GlobalVariable  SET Value =” & GBL_SHOPIFYPRODUCTSCURRENTPAGE & “WHERE [Name] = ‘GBL_SHOPIFYPRODUCTSCURRENTPAGE'”), connection)

cmd2.ExecuteNonQuery()

connection.Close()

End Using

return false

End if


This map is ready to be saved and duplicated.

Make a duplicate of the first map.  On the duplicated map, remove the map pretask because we don’t need to get the page count again.  Add a second Map Post Task of Run Map, choose the first map we created, and mark the Use variables from parent map. 

On the first map, we need to do the same to run the second map.   Map one will run map two, and then map two will run map one and so on until all the pages of products have been processed.

Shopify has endpoints that have counts for many of their resources, so you can apply this setup to any of them to page through your records.  If you are connecting to a web service that does not provide a count endpoint, you can apply the same type of map looping, but the maps will stop running when the source returns no more data.

EDIT: 06/15/20 PTR Added cint() around the globals on the “if” check when comparing the global current page to global total pages.  The reason for this is global variables are stored as strings and if we have a lot of pages then string comparison would fail for page 2 because “2” is greater than “10” as strings but not as integers.