SSIS Magento data Read / Write using REST API Call

Introduction

In this post we will lean SSIS Magento data read / write operations. Magento is a very popular eCommerce platform and they offer JSON based REST API and XML based SOAP API. You can use either API based on your need to automate common integration needs.

We recommend using REST API (JSON API) if possible because they are simpler and faster. We will focus only on JSON API in this article but we do have blog post on how to call SOAP API.

About Calling Magento REST API

If you are new to Magento API we highly recommend to get familiar with Magento REST API here. Magento REST API  offers two ways to authenticate.

1. Token based Authentication
2. OAuth Based Authentication (OAuth 1.0a)

In this article we will look at only Token based approach. In token based approach we have to get new token using admin / customer account information.

We will cover later how to access admin or customer level API later in this article (See HTTP connection configuration).

Configure Connection / Call Magento REST API in SSIS

Now lets look at very simple example how to use Token based method to call Magento REST API in SSIS. We will first configure HTTP connection and then call REST API.

Let’s get started.

1. First, open SSIS Package after installing SSIS PowerPack
2. Drag ZS REST API Task from SSIS Toolbox on Control Flow Designer. Let’s rename task to Get Magento Categories
3. Enter REST API URL you like to call. For example we can use below URL. Replace **your-magento-host** with your own name.
   
   http://**your-magento-host**/index.php/rest/V1/categories

   1	http://**your-magento-host**/index.php/rest/V1/categories

4. Select Url from Connection mode
5. From Connection Dropdown select new ZS-HTTP connection
   

   Create new connection for SSIS REST API Task

6. Configure General Tab of HTTP Connection like below
   URL can be anything for now because we will use override option in next step (On REST API Task).
7. Select Credentials Type as Dynamic Token.
8. Enter Userid / password
   

   Configure Magento REST API Connect for Token based Authentication

9. Now goto Dynamic Token Tab (Or click Configure Link) and configure like below.
10. Enter Token URL for API Login URL field.
    NOTE: If you are a customer and want to access your own account (e.g. view your orders) then replace /admin with /customer  in URL)
    Replace **my-host** with your host name.
    
    http://***my-host***/index.php/rest/V1/integration/admin/token

    1	http://***my-host***/index.php/rest/V1/integration/admin/token

11. Change Method to POST.
12. Enter Body as below (Placeholders are automatically replaced when /token endpoint is called.
    
    {"username":"[$userid$]", "password":"[$password$]"}

    1	{"username":"[$userid$]", "password":"[$password$]"}

13. Select Content Type as Application/Json
    

    Configure Magento Token Request

14. Now click Dynamic Token – Response Tab and configure like below.
    Select Regex and enter Expression as below. This will remove double quotes around token in response.
    
    "(.*)"{{0,1}}

    1	"(.*)"{{0,1}}

    

    Extract Token from response – Remove double quotes around value using Regular Expression

15. Click OK to save connection
16. Now on REST API Task check Use Direct URL Option
17. Click Test Request to confirm its working
    

    Call Magento REST API using SSIS REST API Task

Read data from Magento and Load into SQL Server

Now lets look at how to read Magento data and load into SQL Server. Assume that you like to read all products.

Configure JSON Source (Read Magento REST API)

1. Drag Data flow and double click to edit:
   

   Dragging and dropping Data Flow Task into Control Flow

2. Drag ZS JSON Source on data flow and double click to edit
3. Configure JSON Source like below if you like to get product list for example
   1. Enter URL as below (change http to https if your host support secure channel).
      
      http://***yourhost***/index.php/rest/V1/products?searchCriteria[pageSize]=1000 --- to extract only specific fields do below way -- http://***yourhost***/index.php/rest/V1/products?searchCriteria[pageSize]=1000&fields=items[sku,name]

      1 2 3

      http://***yourhost***/index.php/rest/V1/products?searchCriteria[pageSize]=1000 --- to extract only specific fields do below way -- http://***yourhost***/index.php/rest/V1/products?searchCriteria[pageSize]=1000&fields=items[sku,name]

   2. Check Use Credentials. Use same HTTP connection we created in previous section
   3. In the Filter enter $.items[*] or click Select Filter to Browse and select items node.
   4. Click Preview to confirm
4. Here is how your setup will look like after configuration.
   

   SSIS JSON Source Configuration – Read data from Magento REST API

5. If you expecting more than 1000 rows then you have to set up pagination. Now go to pagination tab and configure as below (Below options are only available in v2.9.1 or higher). If you have older version then check next section for workaround.
   1. Pagination Mode =URL Parameter Mode
   2. Page Num Indicator = searchCriteria[currentPage]
   3. Max Rows Expression = $.total_count
   4. Page Data Expression = $.items[*]
6. Here is how your Magento REST API Pagination Setup will look like.
   

   REST API Pagination using Max Row count Mode

7. Click OK to save JSON Source. In next section we will see how to load data into Target like SQL Server.

Configure OLEDB Destination (Load into SQL Server)

1. Drag OLEDB Destination.
2. Select / Create Connection
3. Select or create NEW target Table (Click NEW button)
4. Click Mapping Tab to map source columns to target table
5. Click OK to save
6. Execute Package

Configure Magento Pagination (For Old Version)

In previous section we configured pagination using newer version (v2.9.1 or higher). But what if you have older version and you dont see that feature. Use below steps for workaround.

Now let’s look at another common scenario when you real lots of data. Let’s say you have 5000 products but API call we saw earlier returns max 1000 rows. In that case we have to keep reading next page until all rows are returned. ZappySys provides many settings to configure various REST APIs out there, unfortunately there is no standard around pagination methods. Since Magento API has some bug in API pagination we have to do following hack. Magento keep sending same data of last page if you try to access page which doesn’t exists. Here is how to overcome that bug.

Configure REST API task to get total row count

First step to configure magento pagination is get total rows returned in our request which you like to paginate (in our case /products).

1. Go to Control Flow designer
2. Drag ZS REST API Task from SSIS Toolbox
3. Let’s rename task to Get Magento Product Count
4. Double click it and configure like below (Change HTTP to HTTPS if needed)
   
   http://***yourhost***/index.php/rest/V1/products?searchCriteria[pageSize]=10

   1	http://***yourhost***/index.php/rest/V1/products?searchCriteria[pageSize]=10

   
5. Go to Response Tab and configure like below. Enter expression as $.total_count
6. Click OK to save

Define Expression for JSON Source – MaxRows Property

Now next step is to define expression on MaxRows property of JSON Rows so we stop once all rows consumed from Paginated response. If you don’t do this it will keep paginating forever 🙁

1. On Control Flow designer select Data Flow which contains JSON Source for Magento
2. Right click and go to Properties of that data flow
3. Find Expression and click Button to add new expression
4. On Expression dialogbox select [Your JSON Source].[MaxRows] and for expression enter variable name which holds total count (e.g. @[User::MaxRows] )
5. Click OK to Save

Configure JSON Source for Magento Pagination

Now last thing we have to do is go to JSON Source and configure few things for pagination.

1. Go to data flow and double click JSON Source
2. Click on Pagination tab and select Pagination by URL Parameter Mode
3. Enter Page name as below
   
   searchCriteria[currentPage]

   1	searchCriteria[currentPage]

4. Click OK to save UI
5. Now run entire page to test (NOTE: Do not execute just data flow because if you do that way, it will not extract total row count)
   

Write data to Magento using SSIS Web API destination

Now let’s look at example to load data into Magento. In below example we will create few sample products.

1. Create new data flow and go to data flow designer
2. Drag and drop ZS CSV Source from SSIS toolbox. You can use any Data source but for simple demo we will use it.
3. Double click CSV source and select Direct Value mode from dropdown. Enter following sample data (Lets create 12 sample products.)
   
   SKU,ProductName PROD-1,Product 1 PROD-2,Product 2 PROD-3,Product 3 PROD-4,Product 4 PROD-5,Product 5 PROD-6,Product 6 PROD-7,Product 7 PROD-8,Product 8 PROD-9,Product 9 PROD-10,Product 10 PROD-11,Product 11 PROD-12,Product 12

   1 2 3 4 5 6 7 8 9 10 11 12 13

   SKU,ProductName PROD-1,Product 1 PROD-2,Product 2 PROD-3,Product 3 PROD-4,Product 4 PROD-5,Product 5 PROD-6,Product 6 PROD-7,Product 7 PROD-8,Product 8 PROD-9,Product 9 PROD-10,Product 10 PROD-11,Product 11 PROD-12,Product 12

4. Now click OK to save UI
5. Now drag ZS Template Transform from toolbox.
6. Connect CSV Source to Template Transform
7. Enter following sample text to build request for new product. Refer to Magento API help file / tutorial to learn more.
   
   JavaScript

   { "product": { "sku": "<%SKU%>", "name": "<%ProductName%>", "attribute_set_id": 4, "price": 25, "status": 1, "visibility": 1 } }

   1 2 3 4 5 6 7 8 9 10

   {   "product": {     "sku": "<%SKU%>",     "name": "<%ProductName%>",     "attribute_set_id": 4,     "price": 25,     "status": 1,     "visibility": 1    } }

8. Notice that when you click Insert Variable > Columns > select desired upstream column placeholder. You can also encode special characters in your data (e.g. new lines) using function like <%MyColumn,JSONENCODE%>
9. Click OK and save UI
10. Now drag ZS Web API Destination from SSIS Toolbox. Configure like below.Enter URL same as before (used to read product), change Method to POST, change content type to Application/JSON.
11. Now run entire flow (You can watch your API requests in Fiddler for debugging)

Debug Magento Web Requests using Fiddler

Click on below article to learn how to debug web requests

How to use Fiddler to analyze HTTP Web Requests

Magento Integration in Other Apps (e.g. Power BI / Informatica / SQL Server)

You can use techniques listed on this page with ODBC Drivers too for integration in other tools like Power BI, SQL Server code, Informatica etc.

Conclusion

In this post we saw how easy it is to perform Magento Integration in SSIS without doing any programming. In few steps you can read or write data in Magento using SSIS PowerPack REST API components. Download FREE Trial and explore many other scenarios.