Magento is a popular open-source e-commerce platform which is highly customizable and has all the features to build a shopping website. Quite often Magento has to work with other applications and for this, Magento provides both REST and SOAP APIs. API provides you with the ability to manage your eCommerce stores by providing calls for working with resources such as customers, categories, products, and sales orders. It also lets you to manage shopping carts and inventory. Both sets of API allows to carry out more or less the same operations to Magento data.
Pentaho ETL is an open-source data integration tool that can help to transfer the data back and forth between Magento platform and other systems. I am providing details to connect to Magento SOAP V2 APIs from Pentaho Spoon.
Let’s discuss about getting all the Sales Orders created in Magento. We will also apply filter to only get Orders with ‘pending’ status in response. Filtering is very important feature required to fetch only incremental data.
I tried using “Web Service Lookup” step in Pentaho ETL to call Magento API. All worked fine except the filters. I was unable to filter the returned records and every time the web service was returning me all the data. Thus, I am explaining here an alternative approach of using SOAP request to call Magento APIs. The high level work flow is given below and explained in details in subsequent sections:
Fig 1: Work Flow
Prerequisite: Setup Magento API Key
Refer to the Magento documentation and setup username and apiKey.
Step 1: Generate Rows Step
Use ‘Generate Rows’ step to create username and apiKey.
Fig 2: Generate Rows
Provide Magento ‘username’ and ‘apiKey’.
Step 2: Login
Load Magento WSDL. Replace <magentohost> with your IP. Select ‘login’ Operation.
Fig 3: Login
Add username and API Key as input as below.
Fig 4: Login Input
Add ‘session ID’ as output variable in loginReturn tab as below.
Fig 5: Login Output
On successful login, you will receive a ‘sessionID’ in response.
Fig 6: Session ID
Step 3: Create SOAP request
Use ‘Modified JavaScript Value’ step to create SOAP request as below:
Fig 7: SOAP Request
Add fieldname ‘requestXml’ as output of JavaScript step. Set its type to string and length to 100000. SOAP request for salesOrderList is as follows:
var request = new XML();
request = <soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:Magento" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
<soapenv:Header/>
<soapenv:Body>
<urn:salesOrderList soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<sessionId xsi:type="xsd:string">{sessionID}</sessionId>
<filters xsi:type="urn:filters">
<filter xsi:type="urn:associativeArray" soapenc:arrayType="urn:associativeEntity[]">
<complexObjectArray>
<key>status</key>
<value>pending</value>
</complexObjectArray>
<complexObjectArray>
<key>order_id</key>
<value>10</value>
</complexObjectArray>
</filter>
<!--Optional :-->
<complex_filter xsi:type="urn:complexFilterArray" soapenc:arrayType="urn:complexFilter[]"/>
</filters>
</urn:salesOrderList>
</soapenv:Body>
</soapenv:Envelope>;
var requestXml=request.toXMLString();
Multiple filters can be applied. I have applied 2 filters here for:
- Status = Pending
- Order_id = 10
Note: You can use tools like soapUI to generate SOAP request template quickly for the required method.
Step 4: SOAP API call using HTTP post step
Select HTTP post step. Provide Magento WSDL URL as below. Replace <magentohost> with your IP.
Specify SOAP request string to ‘Request Entity Field’ option. Result field name is specified as ‘responseXml’ as shown below:
Fig 8: HTTP Post Step
Web service response will be received in ‘responseXml’.
Step 5: End Session
Call End Session method in the end. Load Magento WSDL. Replace <magentohost> with your IP.
Fig 9: End Session
Provide sessionID as input to this method.
Fig 10: End Session Input
Provide output for response.
Fig 11: End Session Output
Reference: Download the sample KTR to call Magento API from Pentaho Spoon (Kettle) here.
