Magento is a leading eCommerce software that allows small and large businesses to build their online stores. As an eCommerce platform, Magento provides an intuitive solution for companies to manage their marketing and sales information and it also stores a large amount of important customer and product data.
In most cases, you interact with customers through multiple applications and the ability to integrate data between different systems can significantly improve your business efficiency. A common requirement is to integrate Magento data with an ERP system such as Microsoft Dynamics 365/NAV.
In order to integrate these two applications, a data integration solution is required. Below we will cover how to retrieve data from Magento and send it to your ERP, with an easy to use, codeless, automated solution. In this example we will be using Microsoft Dynamics 365/NAV, however the same principles can be used to integrate with any other ERP application, or database systems.
For the below solution, the following software must be installed:
- SSIS Integration Toolkit for Magento
- SSIS Integration Toolkit for Microsoft Dynamics 365
- SSDT (SQL Server Data Tools) for design-time development
The SSIS Integration Toolkit for Magento and Microsoft Dynamics 365 are the tools we will be using to facilitate the integration between Magento and Microsoft Dynamics 365/NAV. These solutions leverage Microsoft SQL Server Integration Services (SSIS) as the ETL platform, Microsoft SQL Server will be required when it is time to set-up on-going integration between the two systems. For now, only SSDT is needed for the initial development. If you do not already have SSDT installed, please see our installation guide for more details.
The following solution also takes advantage of SSIS Productivity Pack, though not required it is recommended to ease the development process when preforming the necessary data transformations in this example.
Integrating Magento with Microsoft Dynamics 365/NAV
In this section, we will describe a common integration scenario to demonstrate how to integrate Magento with Microsoft Dynamics 365/NAV. Note that the solution will also work for Dynamics 365 for Financials (simply changing the Server Version to Dynamics 365 for Financials in the NAV Connection Manager and refreshing the metadata to build the connection). In this example, we will connect to Microsoft Dynamics NAV on-premises and focus on the product and customer information in order to demonstrate integrating Magento and other systems.
As we know, Microsoft Dynamics 365/NAV is an ERP system that helps small and medium-sized organization to manage their business operations, such as financial management, service management, and inventory management, so it is a very common requirement to integrate your online sales system, such as Magento, with your online or on-premises ERP system to centralize your data.
In order to work with products and customers, the following two endpoints will be used to retrieve data from Magento:
- Products endpoint: this endpoint will return all products in a list with the product properties, such as name, price, weight, etc.
- Customers/search endpoint: this endpoint will return all qualified customers with corresponding properties based on the search criteria. These properties includes name, email, gender, etc., everything except for shipping information. This information is stored in the child object, addresses, which we will retrieve from to get the shipping information, such as telephone number and address.
We will write the above Magento data to the following Microsoft Dynamics 365/NAV objects:
- Integration_Item_Card object
- Integration_Customer_Card object
In this example, we will use Magento Source component to pull data from Magento and use the Microsoft Dynamics NAV Destination component to write to 365/NAV. As you can see, the data flows are relatively straightforward. The two data flow tasks are shown below:
Figure 1. Data flow of migrating product records.
Figure 2. Data flow of migrating customer records.
When migrating customers, we noticed that the default output only contains the basic customer information but not the shipping information about the customer. In this case, we need to use one of the child objects, the addresses, in order to retrieve the detailed shipping data for each customer record. This will give us a secondary output that contains the shipping dataset we want. After doing that, a Merge Join component is used to join two records together so that we can complete the customer records with their address details.
We are using “Get fullname value” to merge the FirstName, MiddleName and LastName fields from Magento into one field, FullName, as the NAV object we are writing to supports only a single name field.
When it comes to Merge Join transformations, it requires the inputs to be sorted. The Magento Source component features the sorting function, but there are still a couple of steps that need to be done before you link the outputs to the Merge Join component – IsSorted and SortKeyPosition properties to be more specific.
- IsSorted proerpty: this property must be set to True to indicate the data has been sorted.
- SortKeyPosition property: this property must be set for each column of sorted data. The value of this property is used to indicate if a column is sorted, the sorted order and also the sequence when multiple sorted column are used.
Figure 3. Setting IsSorted property in Advanced Editor
Figure 4. Setting SortKeyPosition property in Advanced Editor
Note that you need to change these two properties settings for the secondary output as well. In this example, we change the IsSorted property to True and set the SortKeyPosition to 1 for the id field for both primary and secondary output. The detailed documentation about these two properties can be found on here, which will give you a step-by-step instruction of how to set them up.
We gathered some interesting lessons when creating the package which you can find below. Hopefully these tricks and tips will be helpful in your future development.
- Format address: we found that the address values from Magento is in a [“Test Value”] format, which means you need to use the Premium Derived Column component to get the value. In this example, we utilized the function called GetTokenAtPosition() to retrieve the address value between the quotation marks.
- Replace null values: For the weight field, Magento returns a null value when the product does not have a weight. When writing to NAV, NAV does not take the null value as an input. In this case we used the Premium Derived Column to detect if the input is null, if it is, it gets changed to 0.
- Getting all customers: As far as we know, Magento doesn’t provide an endpoint to get all customers directly, but we can use the customer/search endpoint as a workaround. This endpoint will return all the customers who match the specified criteria. Simply use the “firstname field is NOT NULL” as the condition and then we are able to get all customers with detailed information from Magento. We believe this method can also be applied to other search endpoints, such as coupons and taxRates.
Limitations
When migrating customer records, if the customer has multiple addresses in Magento, you can only get the last address uploaded to Dynamics 365/NAV. This is mainly because Dynamics 365/NAV doesn’t support more than one address stored in its database. In this case, we used the Upsert action to write data to NAV, otherwise you may get duplicate records.
Sample Package
I hope this post is helpful to you when you are creating your integration solution between Magento and other systems. A sample SSIS package is available for download here. The sample package was prepared in a SQL Server 2008 R2 format, and it should automatically upgrade if you are using any newer version of SSIS.
Please feel free to let us know if you have any further comments or suggestions. Our team is always happy to help you achieve more and make your job easier.