Dynamics 365 Integration with SQL Database or Data Warehouse
Learn now to integrate Dynamics 365 CRM with SQL database or data warehouse in a convenient wizard using Skyvia - a no-code cloud data platform. Skyvia supports all major cloud apps, databases (like SQL Server, MySQL, PostgreSQL, etc.) and data warehouses (Snowflake, BigQuery, Redshift, etc.).
In this video we will show two main scenarios:
1. Replicating of Dynamics 365 (on the example of Contact and Account data) to SQL Server on a regular basis.
2. Syncing customer data back from SQL Server to Dynamics 365 Contact object.
Get Skyvia for free: https://skyvia.com
Timestamps:
00:00 - Introduction
00:38 - Create SQL Server and Dynamics 365 connections
02:39 - Replicate data from Dynamics 365 to SQL Server
05:32 - Integrate data from SQL Server to Dynamics 365
08:25 - Visual ETL data pipeline designer
👉 Do you want to learn more about Skyvia? Watch our overview video: https://www.youtube.com/watch?v=41JIlV1bP9c
✅ Check how Skyvia products can help your company in different scenarios: https://skyvia.com/solutions/
🔥 Solve a wide range of data related tasks with our all-in-one cloud data platform! Get started for free https://app.skyvia.com/register
🔔 Subscribe to our channel and get dozens of Skyvia tutorials and useful videos: /@skyvia3342
📧 Do you have any questions or tutorial-related ideas? Feel free to leave a comment or send us an email to sales@skyvia.com
Closed Caption:
Hello and welcome to Skyvia tutorials. In today's
video we will show you how to integrate Dynamics
365, formerly Dynamics CRM, with SQL database
using Skyvia. We will explore two scenarios. The
first involves creating a copy of your Dynamics
365 data in a SQL database. The second involves
syncing data from a database to Dynamics 365. This tutorial assumes you already have a Skyvia account.
You can create one for free by visiting the app Skyvia.com. Before building our Integrations let's
connect to our data sources. Skyvia supports
connections to databases such as SQL Server,
MySQL, PostgreSQL and Oracle. As well as Data Warehouses
such as Snowflake, Google Big Query, Redshift and others. We
use the SQL Server database for our tutorial. There
are two ways to connect to SQL Server with Skyvia:
Direct connection and Agent connection. If your
database is available for external connections,
use a direct connection. To create it click "Create new connection", select SQL Server from the list.
Specify the server address, user ID, and password,
and select the database to connect to. Use the
agent connection if your server is located in the
local computer or network. Agent is an application
that acts as a secure layer between Skyvia and your
database. To create an Agent connection add a new
Agent from the menu. Download the Agent app first
and install it on the computer where the database
is located. Copy the Agent's key and paste it into
the Agent's UI once you run the Skyvia Agent
app. Or download the key file and place it in the
Agent app folder so the Agent will detect the key
automatically. Once you've downloaded and installed
an Agent app, click "Create New", "Connection", choose
SQL Server from the list, and select the Agent
connection mode at the top. Select your Agent from
the dropdown list and specify the server, user
ID, password, and database to which you want to
connect. Now let's connect to Dynamics 365. Create
a new connection from the menu and select the
Dynamics 365 connector. Specify the URL and select
the authentication type. For username and password
authentication, specify your credentials. For OAuth
authentication, signing with your Microsoft account.
Once both connections are ready, let's build our
first integration. With Skyvia, you can replicate
the main Dynamics 365 objects, such as account,
contact, lead, opportunity, and others, as well
as custom objects. We will replicate Dynamics 365
contacts and accounts to the SQL Server database.
Click "Create New" and choose "Replication". Select
Dynamics 365 as Source and SQL Server as Target.
Let's look at replication options. With
the Incremental Updates option enabled,
Skyvia detects record changes since the last
integration run and applies these changes to
your database every run. The Update Schema option
enables comparing the Source and Target metadata.
If there are differences, Skyvia automatically
applies the changes to the Target. With the
Create Tables option enabled, Skyvia will try to
create Source tables in the Target database. If
you select the drop tables check box, Skyvia will
try to drop Source tables in the Target database
before creating them. You can also adjust the
additional replication options if needed. We leave
the options in their default state for our case.
Configure objects for replication on the right.
We will replicate Dynamics accounts and contacts.
Click "Edit" to open the task editor for an object.
Here you can set the target table name and specify
replication settings. You can set four replication
modes for an object. Standard mode is assigned to
each object automatically and applies the default
replication behavior. Replication in History
Mode records every version of each record in
the Source table and send it to the corresponding
table in Target. Use the Resync mode to perform the
full replication for the specific objects that
don't support Incremental Replication. Use Use the Resync
on demand mode to perform the full replication
for the specific objects when running the
replication manually. We select the standard mode
for our example. Skyva also allows you to address
compliance requirements with data hashing. Select
specific object fields to hash, ensuring the secure
transfer of sensitive data. Set filters, if needed,
and save the task. Name the replication and save
it. You can run the replication manually or
based on schedule. Set the replication to
run every day or on specific weekdays, every
few hours or minutes. Thus, you can achieve
near real-time data integration. We will run our
replication manually. When the run is completed,
you will have a copy of the Dynamics 365 data
in your SQL Server database. You can check the
integration results on the Monitor or Logs tabs.
Now, let's move to another integration scenario.
Skyvia enables you to set up integration in any
direction between Dynamics 365 and all supported
sources, including databases and data warehouses.
We will sync SQL Server data to Dynamics 365 using
Skyvia import, the wizard-based ETL tool. Import
allows integrating the supported data sources in
any direction. Its mapping feature helps to sync
data in sources with different data structures
using robust data transformation capabilities. So
let's create a new import from the menu. Set the
Source type. Select the SQL Server connection
as Source and the Dynamics 365 connection as
Target. Add an import task to define what
data to integrate and what transformations
to perform. Select the source object, define
the target object and choose the operation.
Use Insert to add new records, Update to modify
existing ones, and Delete to remove records. The
Upsert operation updates a record, if it exists, or
inserts a new record. We want to add new contacts,
so we select the insert operation and proceed
to mapping. Mapping allows you to transform your
Source data to fit the Target data structure.
There are Source table columns on the left and
Dynamics fields on the right. Skyvia maps the fields
with the same names automatically. You can change
the automatic mapping and map other fields using
available mapping types: Column, Constant, Expression,
and Lookup. Use Column to map a Target and a
source field without transformations. Expression
mapping transforms Source data using functions,
properties, and operators. Use it to convert
Source column data type to match Target field
type, split or concatenate string values, or for
other various transformations. Lookup mapping
fetches a value for the mapped Target field
from a record in any Source or Target object
based on specific conditions. Use Constant to
map a Target field to a specific constant value.
We will use an expression mapping to split the
source name field value into separate values to
match the first name and last name fields and
Target. Now let's save the task. You can add
multiple tasks to import different tables at
once. Let's name our integration and save it.
Set the schedule, if needed, or run the import
manually. When the run is completed check the
integration results on the Monitor or Logs tabs.
in case of errors, click on the run results
to check the failed records. As you can see,
we easily imported a list of customers from the
SQL database to Dynamics 365 with Skyvia. For more
complex scenarios with complicated logic, Advanced
transformation, or integrating several connectors
at once, Skyvia offers Data Flow. Data Flow is a
visual ETL data pipeline designer that can be
used as an import alternative. For example, the
displayed data flow Imports data from Dynamics
365, unites data from separate objects, and loads
them to different targets. It selects data from
Dynamics contact object. Then, it adds the related
details from the account object to the source
data using lookup. And exports the consolidated
data to different databases or data warehouses
simultaneously. Thank you for watching
this Dynamics 365 SQL database integration
tutorial. If you still have any questions,
comment below, and we will help you. To
try Skyvia for free, use the link in the
description. See you in the next videos.
Video Length: 09:22
Uploaded By: Skyvia
Published: 9/20/2024
View Count: 4,476