This is a guest post by maku consulting GmbH.
A MySQL to Microsoft Dynamics 365 integration can bring many benefits to businesses by combining the data from these two platforms. Some potential use cases for this integration are:
- Customer Data Management: Integrating customer data stored in MySQL into Microsoft Dynamics 365 can provide a centralized and up-to-date view of all customer interactions, making it easier to manage customer relationships. The integration can be the first step in a migration towards Microsoft Dynamics 365.
- Sales and Marketing Automation: Integrating sales and marketing data stored in MySQL into Microsoft Dynamics 365 sets the basis for automated lead and opportunity management, allowing businesses to focus on selling.
- Inventory Management: Integrating inventory data stored in MySQL into Microsoft Dynamics 365 can provide real-time visibility into stock levels and help businesses manage their supply chain more effectively.
- Financial Management: Integrating financial data stored in MySQL into Microsoft Dynamics 365 can provide a real-time view of financial transactions and help businesses make informed decisions.
- Reporting and Analytics: Integrating data from both platforms can provide a comprehensive view of business data, making it easier to create reports and perform data analysis.
By integrating MySQL and Microsoft Dynamics 365, businesses can increase efficiency and reduce manual data entry efforts and potential errors from manual processes. Having a single source of truth, including all data from both systems, improves decision-making.
As businesses grow and evolve, their CRM system needs may change as well. For some, an older MySQL-based system may no longer meet their needs and may be holding them back. Microsoft Dynamics 365 provides a modern, flexible, and scalable CRM solution that can meet the needs of businesses of all sizes.
By integrating the data from the old MySQL-based CRM system into Microsoft Dynamics 365, businesses can seamlessly transfer their customer data to a more advanced platform. This integration can help businesses comprehensively view their customer interactions, automate lead and opportunity management, and streamline their sales and marketing processes.
In this blog post, we’ll be exploring the process of connecting a MySQL server to Microsoft Dynamics 365 and how to insert and update any object between the two databases.
Table of Contents
- Preliminary work: define reduced views in your MySQL Server
- Preliminary work: define custom fields in Dynamics 365
- Establishing connections in Skyvia
- Create the data flow
- Add the Source component to your data flow
- Add the Lookup component to your flow and configure it
- Add a conditional split component
- Add the Target components for inserting and updating contacts
- Summary
Contacts are a crucial part of Microsoft Dynamics 365, representing individuals or organizations that a business interacts with. These contacts can include customers, leads, vendors, and partners. By tracking and managing contact information, businesses can gain valuable insights into their relationships with key players and make informed decisions that drive success.
Connecting a MySQL server to Microsoft Dynamics 365 opens new possibilities for businesses, allowing them to import and update object information from other sources. This can be especially useful for businesses with existing customer information databases but want to take advantage of the advanced features and capabilities offered by Microsoft Dynamics 365.
In this blog post, we’ll walk you through the steps of connecting your MySQL server to Microsoft Dynamics 365 and explain how to insert or update objects between the two databases using Skyvia Data Flows. We use the Contact object as an example. Whether you’re new to Skyvia or an experienced user, this guide helps you integrate your data and streamline your operations.
Preliminary work: define reduced views in your MySQL Server
Probably not all columns from your MySQL table are relevant to your use cases in Dynamics 365. We, therefore, recommend defining a view based on your customer’s table, including only the necessary columns, and possibly pre-filtering the view to exclude records you know might make insertion / update difficult. For example, if mandatory fields for Microsoft Dynamics 365 are missing in certain customer records, you could already exclude them in the filtered view.
This tutorial refers to the legacy system’s id as the source_customer_id. However, this may be any field in your database that serves as a unique identifier of a record, such as an e-mail address.
Preliminary work: define custom fields in Dynamics 365
To ensure consistency between your MySQL data and Dynamics 365, we recommend setting up a custom field in Dynamics 365 that contains the legacy system’s unique identifier for the records. This field will also serve as the basis to differentiate between update record and insert record actions in our data flow, as you’ll see later.
Establishing connections in Skyvia
Establish the connections to your MySQL Server as well as your Microsoft Dynamics 365 Environment in Skyvia by clicking New/Connection.
Follow the comprehensive guides provided by Skyvia regarding MySQL connections in direct or in agent-connection mode. Skyvia offers two modes to connect to Microsoft Dynamics 365: OAuth 2.0 or User Name & Password. Using OAuth 2.0, your credentials aren’t stored directly in Skyvia.
Create the data flow
Data Flows allow building integrations with powerful transformations between two or more data sources. Again, click on New and select Data Flow this time.
Add the Source component to your data flow
There are several data flow components: source, target, and transformation. First, we’ll add the Source component.
Give your Source component a name and choose the MySQL connection you have established previously. Choose Execute Command under Action. As you can see in the screenshot above, we are selecting all rows and columns from our pre-defined test_customers view. This means with every execution of the data flow, all rows and columns in the table will be taken into consideration.
Add the Lookup component to your flow and configure it
To establish whether a record needs to be added or updated in Dynamics 365, we’ll perform a Lookup of the new custom field in Dynamics 365. We want to know whether a record from our MySQL data source already exists in our Dynamics 365 environment.
Configure the Lookup component. The screen below shows an exemplary configuration. It’s advisable to configure the Lookup behavior to your use case requirements. As the key, we set the new custom field, which we have defined in the preliminary steps. The result column should be the regular contactID from Dynamics 365.
Further, under Parameters, we have to define a schema where the new custom field in Dynamics 365 is mapped to the customer_id field (here kunden_id) from your MySQL-Server.
Add a conditional split component
The previous Lookup components either output contactID or a null value from Dynamics 365 if no corresponding contactID is found. What we want our dataflow to do, is to insert a new contact if there is no existing contact in Dynamics 365 corresponding to the MySQL contact ID or to update the contact with the newest data from the MySQL data source if it already exists.
We’ll apply the Conditional Split component to achieve this logic:
First, we have to name the component, for example, check_isnull(contactID).
Next, we’ll add the condition on which the data flow split should be performed.
The expression we want to use is isnull(contactID). It returns a logical TRUE if no Dynamics 365 contactID can be found for a corresponding contact ID from the MySQL source. You may check your expression’s functionality with the Validate button.
We name the condition Insert since we’ll insert a contact if the condition is found to be TRUE. The Output Else option will remain at its default setting.
The Conditional Split element should finally look similar to this:
Add the Target components for inserting and updating contacts
After knowing which contacts we’ll insert and update, it’s time to add the Target components to do so:
We’ll first configure the Insert Target component. First, add a name and your Dynamics 365 connection that you set up as part of the preliminary steps. The action should be set to insert, and the table to the corresponding object to be inserted – in our example, contact. Returning will be left empty.
Finally, all fields that should be inserted from your MySQL contacts to the Dynamics 365 contacts have to be mapped:
The Update component has three key differences from the Insert component: First, instead of the Update action, it should include the Insert option. And second, the keys by which the existing contacts in Dynamics 365 can be identified have to be defined under Keys. In our case, those are the contactids from Dynamics 365.
Third, the mapping has to include a mapping of the contactid found in our previous Lookup step property to the contacid in Dynamics 365.
We’d strongly suggest setting up success and failure logs for each of the target components, as described by Skyvia.
That’s it! After setting up the success and failure logs, your data flow should look something like this:
In conclusion, the Skyvia Cloud Platform is a valuable tool for businesses looking to make the transition from an old MySQL-based system to a newer CRM platform like Microsoft Dynamics 365. With its continuous availability of data, flexible customization options, and user-friendly interface, Skyvia makes the transition process smooth, efficient, and worry-free.
Summary
The Skyvia Cloud Platform is a powerful solution for businesses looking to move their data from an old MySQL-based system to a newer CRM platform like Microsoft Dynamics 365. This transition can be a complex and time-consuming process, but with the help of Skyvia, it can be made much smoother and more efficient.
One of the key benefits of using Skyvia is the continuous availability of data. This means that businesses can continue to operate seamlessly even as they make the transition from one platform to another. By using Skyvia, businesses can avoid the downtime and loss of data that can often occur when moving large amounts of information from one system to another.
The Skyvia platform is also highly flexible, allowing businesses to customize the data migration process to meet their specific needs. This includes the ability to map data fields from the old system to the new CRM platform, as well as the ability to automate the process of transferring data from one platform to another.
Another advantage of using Skyvia is its user-friendly interface. With its intuitive design, even those with limited technical skills can easily set up and manage the data migration process. This makes it possible for businesses to complete the transition quickly and with minimal disruption to their daily operations.
maku consulting GmbH
Change is a constant in today’s fast-paced digital world, and it presents both opportunities and challenges for businesses. At maku consulting GmbH, we recognize the importance of staying ahead of the curve and making informed decisions in a complex landscape. As an integration partner of Skyvia, we are well-equipped to support your organization in realizing its full potential. Our expertise covers a range of key areas, including business analysis, software solutions, process digitalization, business intelligence, and project management.
We understand that every company’s needs are unique, and we are committed to working with you to develop tailored solutions that meet your specific requirements. Our goal is to assist you in transforming your business operations and to enable you to engage with your customers in new and innovative ways.
If you’re interested in exploring how we can help your organization succeed in the digital world, we invite you to contact us at hallo@maku.digital.
