- 1 Introduction
- 2 Prerequisites
- 3 About Calling Magento REST API
- 4 Configure Connection / Call Magento REST API in SSIS
- 5 Read data from Magento and Load into SQL Server
- 6 Configure Magento Pagination (For Old Version)
- 7 Write data to Magento using SSIS Web API destination
- 8 Debug Magento Web Requests using Fiddler
- 9 Magento Integration in Other Apps (e.g. Power BI / Informatica / SQL Server)
- 10 Conclusion
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.
PrerequisitesBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
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.
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.
- First, open SSIS Package after installing SSIS PowerPack
- Drag ZS REST API Task from SSIS Toolbox on Control Flow Designer. Let’s rename task to Get Magento Categories
- Enter REST API URL you like to call. For example we can use below URL. Replace **your-magento-host** with your own name.
- Select Url from Connection mode
- From Connection Dropdown select new ZS-HTTP connection
- 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).
- Select Credentials Type as Dynamic Token.
- Enter Userid / password
- Now goto Dynamic Token Tab (Or click Configure Link) and configure like below.
- 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.
- Change Method to POST.
- Enter Body as below (Placeholders are automatically replaced when /token endpoint is called.
- Select Content Type as Application/Json
- 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.
- Click OK to save connection
- Now on REST API Task check Use Direct URL Option
- Click Test Request to confirm its working
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)
- Drag Data flow and double click to edit:
- Drag ZS JSON Source on data flow and double click to edit
- Configure JSON Source like below if you like to get product list for example
- Enter URL as below (change http to https if your host support secure channel).
123http://***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]
- Check Use Credentials. Use same HTTP connection we created in previous section
- In the Filter enter $.items[*] or click Select Filter to Browse and select items node.
- Click Preview to confirm
- Enter URL as below (change http to https if your host support secure channel).
- Here is how your setup will look like after configuration.
- 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.
- Pagination Mode =URL Parameter Mode
- Page Num Indicator = searchCriteria[currentPage]
- Max Rows Expression = $.total_count
- Page Data Expression = $.items[*]
- Here is how your Magento REST API Pagination Setup will look like.
- 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)
- Drag OLEDB Destination.
- Select / Create Connection
- Select or create NEW target Table (Click NEW button)
- Click Mapping Tab to map source columns to target table
- Click OK to save
- Execute Package
- 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
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).
- Go to Control Flow designer
- Drag ZS REST API Task from SSIS Toolbox
- Let’s rename task to Get Magento Product Count
- Double click it and configure like below (Change HTTP to HTTPS if needed)
- Go to Response Tab and configure like below. Enter expression as $.total_count
- 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 🙁
- On Control Flow designer select Data Flow which contains JSON Source for Magento
- Right click and go to Properties of that data flow
- Find Expression and click Button to add new expression
- On Expression dialogbox select [Your JSON Source].[MaxRows] and for expression enter variable name which holds total count (e.g. @[User::MaxRows] )
- 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.
- Go to data flow and double click JSON Source
- Click on Pagination tab and select Pagination by URL Parameter Mode
- Enter Page name as below
- Click OK to save UI
- 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.
- Create new data flow and go to data flow designer
- Drag and drop ZS CSV Source from SSIS toolbox. You can use any Data source but for simple demo we will use it.
- Double click CSV source and select Direct Value mode from dropdown. Enter following sample data (Lets create 12 sample products.)
12345678910111213SKU,ProductNamePROD-1,Product 1PROD-2,Product 2PROD-3,Product 3PROD-4,Product 4PROD-5,Product 5PROD-6,Product 6PROD-7,Product 7PROD-8,Product 8PROD-9,Product 9PROD-10,Product 10PROD-11,Product 11PROD-12,Product 12
- Now click OK to save UI
- Now drag ZS Template Transform from toolbox.
- Connect CSV Source to Template Transform
- Enter following sample text to build request for new product. Refer to Magento API help file / tutorial to learn more.
- 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%>
- Click OK and save UI
- 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.
- 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
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.
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.