How to Integrate Dynamics CRM and SQL Server: Step-by-Step Guide

It’s impossible to imagine a company that doesn’t implement a customer relationship management (CRM) system nowadays. In fact, around 87% of people using a CRM reported that it had an incredibly positive impact on sales growth. One of the most widely used and popular CRM systems nowadays is Microsoft Dynamics 365, which includes Sales, Customer Service, Marketing, and other CRM modules.

Even though data in Microsoft Dynamics is safely stored and accessible, there might be a need to transfer or sync it with another data source. In particular, SQL Server RDBMS is the tool of interest here because it’s the most commonly used and highly-rated database.

This article aims to explore Dynamics CRM SQL Server symbiosis. It focuses on the ways to integrate these sources. Thus, we present Dynamics CRM SQL Server best practices using API, SSIS service, ODBC driver, and a third-party tool, Skyvia. 

Table of Contents

1. Integration via Direct API
2. Integration via SSIS Service
3. Integration via ODBC Driver
4. Integration via Skyvia
   • Replicate Data (ELT)
   • Import Data (ETL)
   • Synchronize Data
5. Benefits of Dynamics CRM и SQL Server Connection
6. Comparing Methods
7. Conclusion

Integration via Direct API

Microsoft provides API (V2.0) for connecting Dynamics 365 with other applications and services. This is typically done with so-called Connect apps that establish point-to-point connections between services. Such applications rely on standard REST API for data exchange.

Official documentation provided by Microsoft also includes examples of such Connect apps along with all the needed information on APIs. Obviously, experienced developers must be in charge of creating such connections between Dynamics CRM and SQL Server. This requires plenty of time and testing, though it offers unlimited customization, which could be a priority for some companies. Overall, direct API connection demands considerable investments and professional expertise.

Integration via SSIS Service

When asked how Dynamics CRM connects to SQL Server, the utilization of Microsoft tools comes into mind as both products are developed by this techno titan. SQL Server Integration Services (SSIS) could be an excellent solution for combining these tools together since it’s dedicated explicitly to designing ETL pipelines for data integration and transformation.

Prerequisites

1. Download Microsoft Visual Studio with the Data storage and processing toolset selected upon installation.

2. In Visual Studio, download the SQL Server Integration Services Projects extension.

Step-by-step instructions for integration using SSIS

1. Open Visual Studio and click Create a New Project.
2. Select the Integration Services Project type and click Next.

3. Configure the project settings and click Create.
4. Select the Data Flow task.

5. Click Source Assistant and select SQL Server. Apply other configuration settings if needed.

6. From the left panel of the project, under Sources, select SQL Server and drag it to the central task area.
7. In the Solution Explorer panel on the right of the main project window, right-click on the Connection Manager and select New Connection.
8. Select Dynamics CRM and click Add.
9. Click Test Connection.
10. From the left panel of the project, under Destinations, select Microsoft CRM and drag it to the central task area.
11. Connect source and destination blocks as follows. If there’s a need to transform data, select them from the SSIS Toolbox panel and put them between the source and destination.

12. In the Solution Explorer panel, find the task and right-click on it. Select Execute Package.

Similar procedures can be done reversely – with Dynamics CRM as a source and SQL Server as a destination.

Integration via ODBC Driver

Another way to connect both instances is to use the Devart ODBC Driver for Dynamics 365. It allows the creation of a linked server in SQL Server Management Studio that provides permission to execute SQL commands and read data from Dynamics 365. In this case, only mono-directional data transfer is expected, obviously. 

Prerequisites

• Install and configure the Devart ODBC driver for Dynamics.

Step-by-step instructions for integration with ODBC driver

To create a linked server for Dynamics 365 in SQL Server Management Studio, proceed with the following steps:

1. Launch SSMS and select the required SQL Server instance.
2. In Object Explorer, expand the Server Objects section. Then right-click on Linked Server and select New Linked Server.

3. Configure a newly created linked server:
   • In the Linked server field, type the name of the server.
   • Select Other data source from the Server type drop-down list.
   • Select Microsoft OLE DB Provider for ODBC drivers from the Provider drop-down list.
   • In the Data source field, type DSN name (e.g., Devart ODBC Driver for Dynamics 365). Alternatively, it’s possible to type the ODBC Driver connection string in the Provider field.

The linked server appears under Linked Servers in Object Explorer. Executing distributed queries and accessing Dynamics 365 databases via SQL Server is now possible.

Integration via Skyvia

Skyvia is a cloud-based platform that offers several ways to integrate Dynamics and SQL Server data. In this section, we mainly focus on the Import component (ETL solution), Replication component (ELT solution), and Synchronization (bi-directional sync solution).

Prerequisites

Before going through the details of integration scenarios, take some preparatory actions:

1. Open Skyvia and log into the system or sign up to create an account.
2. Connect to Microsoft Dynamics from Skyvia – see detailed step-by-step instructions.

Connect to SQL Server from Skyvia – see detailed step-by-step instructions.

NOTE: Select the Agent connection mode for SQL Server to establish a secure channel. Make sure to download SQL Server Agent, install it, and run it as Administrator beforehand.

Replicate Data (ELT)

Skyvia’s Replication scenario helps users copy data from one source to another. Here, we’ll explore the case of data replication from Microsoft Dynamics 365 to SQL Server. Note that there’s no need to create tables in SQL Server, as Skyvia will do that automatically. Moreover, the replication scenario doesn’t allow performing any data transformation.

1. Click +NEW in the top menu and select Replication in the Integration column.
2. Select Dynamics connection as a source and SQL Server as a target.
3. Select the objects to replicate.
4. Select Incremental Updates under Options. That way, Skyvia copies all data only when the replication procedure is executed for the first time. During subsequent replication procedures, the system checks which Dynamics data was updated and then will apply these changes to the SQL Server tables.
5. To run the task on a regular basis, click Schedule to define parameters for its automatic execution.
6. Click Create.

Import Data (ETL)

Skyvia’s Import is a flexible ETL tool for data migration between different platforms. It allows loading data from CSV files, cloud apps, or relational databases to other cloud apps or relational databases. In contrast to Replication, the Import integration offers data transformation and mapping capabilities along with other advanced features for working with data.

With Skyvia, you can easily configure data migration between Microsoft Dynamics and SQL in any direction. Here, we particularly review how to load data from SQL Server to Microsoft Dynamics. This is a prevalent case as it allows complementing the cloud app data with financial or contact details, for instance, which greatly simplifies the work of sales teams.

1. Click +NEW in the top menu and select Import in the Integration column.
2. Under the Source type section, select Database or cloud app and find SQL Server in the Connection drop-down list.
3. Under the Target section, select Microsoft Dynamics from the Connection drop-down list.

4. Click Add new in the Tasks section on the right.
5. In the task editor, it’s possible to define filters, select data import operations (INSERT, UPSERT, UPDATE, or DELETE), and define mapping types (Column, Expression, Lookup, Constant, Relation, External ID, etc.).
6. After all configuration settings are ready, click Save.
7. Click Create.
8. To run the task on a regular basis, click Schedule to define parameters for its automatic execution.

Note that it’s also possible to transfer data in the opposite direction. Moreover, After uploading/transferring data with the help of Skyvia to SQL Server, users can then load the prepared data into BI/AI etc tools.

Synchronize Data

Skyvia’s Synchronization component aims to sync data between cloud applications (Microsoft Dynamics 365) and relational databases (SQL Server) in both directions. It allows synchronizing data even of a different structure, preserving all data relations and providing powerful mapping settings configuration.

​​When the synchronization task runs for the first time, the system doesn’t check whether the records in data sources are identical but simply copies data from one side to another. This may cause duplicate records in the destination system, so it’s crucial to ensure it has no records yet. During subsequent sync operations, if a record is modified or deleted in one source, the system modifies or deletes the data mapped to this record in another source, and vice versa.

1. Click +NEW in the top menu and select Synchronization in the Integration column.
2. Select Dynamics connection as a source and SQL Server as a target.
3. Click Add new in the Tasks section on the right. In the task editor, select an object from the Source drop-down list and the corresponding data field on the target side.
4. Define data mapping settings in the Column Definition tab for both directions and save the task.
5. Click Schedule to define parameters for its automatic execution.
6. Click Create.

To start synching right away, click Run and go to the Monitor tab to check the progress.

Benefits of Dynamics CRM и SQL Server Connection

Businesses need to consider Dynamics 365 & SQL Server integration because of its tangible and intangible benefits. Some of the most significant ones are provided below.

1. Data visibility. As SQL Server is usually seen as an enterprise ERP system, companies can take data from there to complement Dynamics CRM. Thus, Dynamics contains all the data needed for departments working with customers, which enhances the overall data visibility.
2. Enhanced collaboration. Having integration tools in place makes the collaboration between departments more fruitful.
3. Balanced decision-making. Loading data from one source to another and keeping it up-to-date provides a strong basis for analysis. As SQL Server as well as Dynamics easily connect to another Microsoft product named Power BI, driving analytics becomes easier, and thus makes decisions weighted.

Comparing Methods

We have discussed the fundamental methods for bringing SQL Server and Dynamics 365 together. However, how could one know whether to use ODBC Driver for Dynamics 365, direct API, or Skyvia? We have prepared a comparison table of all the methods provided in this article to take the weight off your shoulders.

It’s evident that direct API allows businesses to customize almost everything in the data integration processes, though it takes much time and resources for that. SSIS also requires technical expertise, but it appears much simpler than direct API and costs less. ODBC driver and Skyvia, in contrast to the two previous methods, rely more on the user interface of desktop or web solutions, which makes them really convenient and fast in use. Both solutions don’t cost much, and the pricing depends on particular business needs. 

Conclusion

The era of direct API integration is left behind, even though this approach is still used for connecting apps. Such innovative data integration platforms as Skyvia come into play, substituting traditional methods. Businesses no longer need to spend time and money deploying, implementing, and maintaining complex integration scenarios but instead try Skyvia with a clear visual interface and multiple components for completing various tasks. Moreover, this tool is affordable for every business due to its simplicity and pricing. Don’t hesitate to try Skyvia today!