<img alt="" src="https://secure.leadforensics.com/150446.png " style="display:none;">

Connecting to Magento API with Pentaho ETL (Spoon)

Anuradha Bankar May 26, 2016

pentaho Magento Magento API Pentaho ETL Pentaho Spoon

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/">
      <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[]">      
            <!--Optional :-->
            <complex_filter xsi:type="urn:complexFilterArray" soapenc:arrayType="urn:complexFilter[]"/>
 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.

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.