--- title: Connecting to Magento API with Pentaho ETL (Spoon) description: Author is explaining the steps to connect Magento API with Pentaho ETL (Spoon) image: https://blog.e-zest.com/hubfs/workflow-1.jpg --- [![e-Zest logo\_An accion lab company\_Colour](https://blog.e-zest.com/hubfs/images/Logos%20with%20Tagline/Corporate/SVG/e-Zest%20logo_An%20accion%20lab%20company_Colour.svg "e-Zest logo_An accion lab company_Colour")](https://www.e-zest.com/) - [WORK](https://www.e-zest.com/our-work) - [SERVICES](https://www.e-zest.com/our-services) - [Product Engineering](https://www.e-zest.com/digital-product-engineering-services) - [Enterprise Data Services](https://www.e-zest.com/enterprise-data-services) - [Digital Commerce](https://www.e-zest.com/digital-commerce-services) - [Digital Operations](https://www.e-zest.com/digital-operations) - [Digital Consulting](https://www.e-zest.com/digital-transformation-consulting-services) - [Digital Experience Design](https://www.e-zest.com/digital-experience-design) - [INSIGHTS](https://blog.e-zest.com/) - [CAREERS](https://www.e-zest.com/careers) - [ENTERPRISE DATA SERVICES](https://www.e-zest.com/enterprise-data-services) - [GENERATIVE AI](https://www.e-zest.com/generative-ai) - [Healthcare](https://healthcare.e-zest.com) - [Contact Us](https://www.e-zest.com/contact-us) - ![Elevating Product Engineering with AI and ML](https://blog.e-zest.com/hubfs/Elevating%20Product%20Engineering%20with%20AI%20and%20ML.jpg) [BLOGS](https://blog.e-zest.com) #### Elevating Product Engineering with AI and ML As technology evolves and customer choices broaden, there is an increasing ... [Read more](https://blog.e-zest.com/elevating-product-engineering-with-ai-and-ml) - ![e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024](https://blog.e-zest.com/hubfs/e-Zest%20%E2%80%93%20An%20Accion%20Labs%20Company%20-%20Gets%20Recognition%20as%20a%20Contender%20in%20the%20ISG%20Provider%20LensTM%20Study%20on%20Digital%20Engineering%20Services%202024.jpg) [NEWS](https://blog.e-zest.com/news) #### e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024 We are delighted to announce that e-Zest – An Accion Labs Company has been ... [Read more](https://blog.e-zest.com/news/e-zest-an-accion-labs-company-gets-recognition-as-a-contender-in-the-isg-provider-lens-study-on-digital-engineering-services-2024) - ![e-Zest Solutions supports Digital Pune Mission](https://blog.e-zest.com/hubfs/blog-files/Digital%20Literacy%20Bus%20e-Zest.png) [CSR](https://blog.e-zest.com/csr) #### e-Zest Solutions supports Digital Pune Mission **Pune, India:** e-Zest Solutions, a leading digital technology innovation ... [Read more](https://blog.e-zest.com/csr/e-zest-solutions-supports-digital-pune-mission) - [Work](https://www.e-zest.com/our-work) - [Services](https://www.e-zest.com/our-services) - [Insights](https://blog.e-zest.com) - [Careers](https://www.e-zest.com/careers) - [Enterprise Data](https://www.e-zest.com/enterprise-data-services) - [Generative AI](https://www.e-zest.com/generative-ai) - [Innovations](https://www.e-zest.com/innovations) - [Healthcare](https://healthcare.e-zest.com) - [About](https://www.e-zest.com/about-us) - [Contact](https://www.e-zest.com/contact-us) - [Blog](https://blog.e-zest.com/) - [News](https://blog.e-zest.com/news) - [CSR](https://blog.e-zest.com/csr) - [![Linkedin Icon](https://blog.e-zest.com/hubfs/icons/linkedin_icon_white.svg)](https://www.linkedin.com/company/e-zest-solutions/) - [![Twitter Icon](https://blog.e-zest.com/hubfs/twitter-logo-silhouette%20(5).svg)](https://twitter.com/ezest) - [![Facebook Icon](https://blog.e-zest.com/hubfs/facebook-app-logo%20(4).svg)](https://www.facebook.com/61259256638) - [![Instagram Icon](https://blog.e-zest.com/hubfs/instagram-logo%20(4).svg)](https://www.instagram.com/ezestsolutions/) - [![Youtube Icon](https://blog.e-zest.com/hubfs/icons/youtube_icon_white.svg)](https://www.youtube.com/user/ezestcorporate) [![Go to top icon](https://blog.e-zest.com/hubfs/AA%20Assets/top%20button.svg)](https://blog.e-zest.com/connecting-to-magento-api-with-pentaho-etl-spoon#top) Hit enter to search or ESC to close Insights e-Zest members share technology ideas to foster digital transformation. - [Blog](https://blog.e-zest.com) - [News](https://blog.e-zest.com/news) - [Podcasts](https://www.e-zest.com/podcasts) - [Healthcare](https://blog.e-zest.com/healthcare) - [CSR](https://blog.e-zest.com/csr) - [Whitepaper](https://blog.e-zest.com/whitepaper) - [Playbook](https://blog.e-zest.com/playbook) - [Media Kit](https://www.e-zest.com/media-kit) # Connecting to Magento API with Pentaho ETL (Spoon) [Anuradha Bankar](https://blog.e-zest.com/author/anuradha-bankar) May 26, 2016 [pentaho](https://blog.e-zest.com/topic/pentaho) [Magento](https://blog.e-zest.com/topic/magento) [Magento API](https://blog.e-zest.com/topic/magento-api) [Pentaho ETL](https://blog.e-zest.com/topic/pentaho-etl) [Pentaho Spoon](https://blog.e-zest.com/topic/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: ![workflow-1.jpg](https://blog.e-zest.com/hs-fs/hubfs/workflow-1.jpg?width=750&height=137&name=workflow-1.jpg "workflow-1.jpg") 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. ![Step-1.jpg](https://blog.e-zest.com/hs-fs/hubfs/Step-1.jpg?width=746&name=Step-1.jpg "Step-1.jpg") Fig 2: Generate Rows Provide Magento ‘username’ and ‘apiKey’. **Step 2: Login** Load Magento WSDL. Replace \ with your IP. Select ‘login’ Operation. ![step-2.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-2.jpg?width=750&height=236&name=step-2.jpg "step-2.jpg") Fig 3: Login Add username and API Key as input as below. ![step-3.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-3.jpg?width=576&height=206&name=step-3.jpg "step-3.jpg")         Fig 4: Login Input Add ‘session ID’ as output variable in loginReturn tab as below. ![step-4.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-4.jpg?width=512&height=193&name=step-4.jpg "step-4.jpg") Fig 5: Login Output On successful login, you will receive a ‘sessionID’ in response. ![step-5.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-5.jpg?width=705&height=172&name=step-5.jpg "step-5.jpg") Fig 6: Session ID **Step 3: Create SOAP request** Use ‘Modified JavaScript Value’ step to create SOAP request as below: ![step-6.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-6.jpg?width=750&height=327&name=step-6.jpg "step-6.jpg") 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 = ``` ```    ``` ```    ``` ```       ``` ```          {sessionID} ``` ```          ``` ```                    ``` ``` ``` ```     status ``` ```     pending ``` ``` ``` ``` ``` ```      order_id ``` ```      10 ``` ``` ``` ```             ``` ```             ``` ```             ``` ```          ``` ```       ``` ```    ``` ``` ; ``` ```  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 \ with your IP. Specify SOAP request string to ‘Request Entity Field’ option. Result field name is specified as ‘responseXml’ as shown below: ![step-7.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-7.jpg?width=750&height=479&name=step-7.jpg "step-7.jpg") 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 \ with your IP. **![step-8.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-8.jpg?width=750&height=237&name=step-8.jpg "step-8.jpg")** Fig 9: End Session Provide sessionID as input to this method. ![step-9.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-9.jpg?width=505&height=178&name=step-9.jpg "step-9.jpg") Fig 10: End Session Input Provide output for response. ![step-10.jpg](https://blog.e-zest.com/hs-fs/hubfs/step-10.jpg?width=492&height=185&name=step-10.jpg "step-10.jpg") Fig 11: End Session Output Reference: Download the sample KTR to call Magento API from Pentaho Spoon (Kettle) [here](https://blog.e-zest.com/hubfs/Call_Magento_SOAP_API_V2.ktr). - - - ### Similar Blog [Your Big Data Future is Safe with Pentaho In today’s business world, Big Data has become a very key part of strategic business decisions. To make the most out of the ... By Shashank Venkat Jan 04, 2016](https://blog.e-zest.com/your-big-data-future-is-safe-with-pentaho) [Tech Digest – Week 27, 2013 **Auto scaling in cloud computing** By Corporate Jul 05, 2013](https://blog.e-zest.com/tech-digest-week-27-2013/) [Pentaho configuration for MySQL **Steps to change Pentaho preconfigured hypersonic database to MySQL** By Shailesh Kulkarni Dec 26, 2012](https://blog.e-zest.com/pentaho-configuration-for-mysql/) [![Saiku Analytics (BI) Integration with Pentaho and Liferay](https://blog.e-zest.com/hs-fs/file-3395201834-png/blog-files/login-in-pentaho.png) Saiku Analytics (BI) Integration with Pentaho and Liferay **Objective:** By Shailesh Kulkarni Dec 24, 2012](https://blog.e-zest.com/saiku-analytics-bi-integration-with-pentaho-and-liferay/) [![Using AngularJS with Pentaho Dashboard](https://blog.e-zest.com/hubfs/img-1.jpg) Using AngularJS with Pentaho Dashboard Pre Requisite: Knowledge of AngularJS is needed. By Anuradha Bankar Oct 04, 2016](https://blog.e-zest.com/using-angularjs-with-pentaho-dashboard) [![Hichert chart in MicroStrategy](https://blog.e-zest.com/hubfs/img-1-10.png) Hichert chart in MicroStrategy **What is HICHERT®SUCCESS?**Prof Dr. Rolf Hichert wanted to make business communication most successful. He initiated the SUCCESS ... By Anuradha Bankar Jun 02, 2016](https://blog.e-zest.com/hichert-chart-in-microstrategy) [![Elasticsearch vs Graph Database](https://blog.e-zest.com/hs-fs/file-3419272691-jpg/blog-files/elastic-search-vs-graph-database.jpg) Elasticsearch vs Graph Database Recently, I worked on a project which involved building a genealogy portal to gather information about individuals and their ... By Anuradha Bankar Sep 24, 2015](https://blog.e-zest.com/elasticsearch-vs-graph-database/) [Sharding and Replication in MongoDB Sharding and Replication have become important tool in today’s application database design. We have couple of ongoing projects ... By Anuradha Bankar Jan 23, 2014](https://blog.e-zest.com/sharding-and-replication-in-mongodb/) ## Helping Organizations Thrive in the Digital Age [Contact Us](https://www.e-zest.com/contact-us) - [About](https://www.e-zest.com/about-us) - [Services](https://www.e-zest.com/our-services) - [Work](https://www.e-zest.com/our-work) - [Insights](https://blog.e-zest.com/) - [Healthcare](https://healthcare.e-zest.com/) - [Accion Labs](https://www.accionlabs.com/) - - - - - - Dallas - Detroit - Hannover - Vienna - London - Pune © 2025 e-Zest Solutions - [Privacy Policy](https://www.e-zest.com/privacypolicy) - [Cookie Policy](https://www.e-zest.com/cookie-policy) Contact us Contact us 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.