30 May 2017

Exchange on-premise data between SQL Server and Microsoft Dynamics 365 for Operations using Microsoft Flow

by | posted in: Microsoft Dynamics | 0

By:  Rahul Mohta

In this article I will share knowledge about exchanging data between Microsoft Dynamics 365 for Operations, running on Azure, and your local files or local SQL data.

To achieve this goal we will leverage Microsoft Flow, the workflow engine available on Azure. This cloud service works seamlessly with other Microsoft Cloud services (e.g., Office 365, Power BI, PowerApps), enabling information flow that can meet the latest digital business demands.

Dynamics 365, Flow, and SQL Server seamlessly connect with each other and bring data to life in almost any scenario. You can read information from any Azure-based applications, Common Data Services (CDS), a local file system, and SQL Server (Azure or local), as well as from applications like Twitter, Google and Facebook, which makes it easier for tech-savvy information seekers to use them in their jobs in a seamless experience.

Below are some select features of Flow:

Microsoft Flow features / Source

Microsoft Flow is fast gaining popularity as a part of Office 365. Since its launch, dozens of connectors have been launched for varied applications and data sources including Dynamics 365 for Operations. Flow is built on top of Azure Logic Apps and does not require an Azure subscription for basic use purposes.

Flow as an Office 365 service can be used to create templates that enable power users to grab data, process it, and send it to a destination in an easier way, thereby increasing productivity.

With the above background, let’s examine a business scenario wherein data available locally or on-premise needs to be sent to your cloud ERP (Dynamics 365 for Operations). The local/on-premise data could be in any form, for example, files, SQL or SharePoint. We’ll consider data present in local SQL which needs to be imported into Dynamics 365 for Operations. While I’m using a one-time import, Flow’s capabilities could be used to schedule the imports as aa recurring integration.

Below are key steps in configuring Flow and making a connection with Dynamics 365 for Operations:

  • Start by verifying the local database details and identifying the table we would like to import/integrate data.
  • Next, create an on-premise data connection gateway.

A gateway is needed to create a bridge between your on-premise data and the cloud. This gateway can be used by any or all of the three services (Flow, PowerApps and Power BI). Below is a representation of data flow leveraging an on-premises gateway and Microsoft Azure services.

 On-premises data gateway / Source

You can download the gateway from this link.

After download, the installation is quite simple, and will ask for a credential (an Office 365 account). This registers the gateway with Azure cloud services. You will also need to specify a gateway name and a recovery key.

  • After installing the gateway, it needs to be configured as pictured below, which depicts the configuration screen of the on-premises gateway:

Once the gateway is configured, your local data (e.g., files, SQL, SharePoint) can be made available to Azure and hence Dynamics 365 for Operations.

  • Now let’s make our Microsoft Flow recognize this gateway.

First we sign into Flow.Microsoft.com, and create a connection specifying the gateway. Click on the gear symbol to the top right and select “Connections,” then on “Create connection.” Flow will give you a list of available connections/datasources as shown below:

Select SQL Server (Microsoft) and click on the + (plus) sign.

  • Ensure that “Connect via on-premise data gateway” is checked, then provide your SQL details as shown in visual below, including:
    • SQL server name
    • Database name\instance name
    • “Basic” authentication type as (you must enable the user with appropriate access to the local SQL).

Note: the following data sources are supported by the on-premises gateway:
  • SQL Server
  • SharePoint
  • Oracle
  • Informix
  • Filesystem
  • DB2

Now, verify your connection in the below screen as pictured:

Note: You could also verify the gateway under the gear symbol and “Gateways” option.

Once the connection is established, you are now ready to build your Flow/PowerApps and work with the local and cloud data together.

  • Following are the configurations steps to create a new Flow without any template:

Click on “My Flows” in the title bar.

Click on “Create from Blank”

You’ll be given a list of choice as triggers and actions as shown below:

We will select Schedule as our trigger so that we’ll be able to run the Flow at will or at scheduled intervals.

Next, click on “New step” and select “Add an action.” Here, we will get data using an on-premise gateway from local SQL Search for SQL, and select the option of Get SQL data rows as an action.

Here you need to specify the table in your local SQL to pull data from. You can also specify additional attributes for filtering data, order, and so forth.

  • After data is configured to be pulled in the Flow using the local SQL table, it’s time we use the data to process in Dynamics 365 for Operations.

Here we would create records in Dynamics 365 for Operations which need to be specified by adding a new step and choosing an action as show below:

Specify your Dynamics 365 for Operations URL as Instance

  • Once the instance is selected, you can select any of the Public data entities to use the local data.

Here we would map the customergroup column from local SQL table to the CustomerGroup field from data entity “CustomerGroups”:

Note: We are using dynamic content based approach to get the values from local SQL and then creating records in Dynamics 365 for Operations. Hence for easier understanding purpose, this should be understood as, “for each record in local SQL, create a new record in Dynamics 365 for Operations”.

  • Save the Flow and verify the same in the “My flows” section.

You can wait for the time interval specified or manually run the flow on demand. You can configure the recurrence pattern and make it a one-time import or a recurring integration (to and fro).

  • Once the flow runs, you can verify the records are created in Dynamics 365 for Operations as shown below:

I will conclude this article by sharing the out-of-the-box services available from Flow for Dynamics 365 for Operations.

 Dynamics 365 for Operations templates / Source

Also the same on-premise data gateway can be leveraged in PowerApps as shown below for a great user experience.

About Rahul Mohta

Rahul Mohta is one of the advisory members of Real Dynamics.  He has cultivated diverse and rich experiences working with global Dynamics AX customers and partners, enabling them to realize the full value of their ERP platforms. Rahul’s 15 years of experience spans across multiple geographies (Americas, Europe, Asia, India) and multiple domains – Financials, Supply Chain, Distribution, Manufacturing, Warehousing, Retail and Professional Services. Prior to joining Real Dynamics, he played consulting roles for Microsoft and Infosys. For more information on Real Dynamics services, please contact us at info@realdynamics.com.