by Dibyendu Datta | February 09, 2023

Replace the Deprecated Microsoft Data Export Service with CData

In November 2021, Microsoft announced the deprecation of Data Export Service (DES), an add-on feature available via Microsoft AppSource which provides the ability to replicate data from Microsoft Dataverse to an Azure SQL store in a customer-owned Azure subscription. DES facilitated data replication from Dynamics 365 instances and was used by many for data archiving purposes. Data Export Service reached end-of-support and end-of-life a year from the announcement date, in November 2022.

Since the deprecation, Microsoft announced Azure Synapse Link for Dataverse as the replacement for DES. However, Azure Synapse Link for Dataverse does not offer the same functionality and there is no control over the database tables populated on the Synapse side. Because of such limitations, many Dynamics 365 clients who were relying on DES have been looking for a more appropriate replacement solution.

In this blog post, we'll show you how to use CData’s connectivity solutions to efficiently achieve the same replication task. CData products provide alternative solutions for DES in two ways:

• CData SSIS Components have the common elements of SSIS Source, Lookup, and Destination components for Dynamics CRM and Dynamics 365 (and 250+ other data sources).
• CData Sync offers a point-and-click interface to replicate Dynamics 365 to Azure SQL (and a wide range of other on-premises and cloud applications and data stores).

To explain our tools better, we demonstrate building an incremental ETL process using CData solutions to load Dynamics CRM data into SQL Server (OLE DB). Note that an incremental load is the process of loading only new or updated records into a data warehouse. It is a common technique used to keep the data warehouse up to date without having to perform a full refresh of the data each time the ETL process runs.

SSIS Components

CData offers SSIS Data Flow components for Dynamics CRM, Dynamics 365, and over 250 other data sources. Each of these components is packed with three elements: Source, Lookup, and Destination. Once installed, these components can be seen under the SSIS Toolbox of SSIS Package inside the Data Flow tab of Visual Studio Project.

Using Dynamics CRM SSIS Components to Implement Incremental Load

You can use the CData Dynamics CRM components to perform incremental loads and data updates from the source in SSIS. The Dynamics CRM Source component allows you to retrieve data from your Dynamics CRM account based on a specific entity and filter condition.

Here's a general outline of using the CData Dynamics CRM Source component to implement incremental loads:

1. Set up your SSIS project and add a Data Flow Task to the control flow.
2. In the Data Flow Task, add CData Dynamics CRM Source component and configure it to connect to your Dynamics CRM account.
3. Select the entity that you want to retrieve data from the source.
4. Add destination components – SQL Server-OLE DB Destination & SQL Server-OLE DB Command – to the Data Flow Task and configure it to load or update the data into your destination table.
5. Include a Lookup for Match (OLE DB Command) to update the destination table, and No Match Output (OLE DB Destination) to load data based on the changes in source entity data.
6. Add necessary conditions in the destination components for data retrieval.
7. Run the package to perform the incremental load and source data updates. The Dynamics CRM Source component will retrieve only the modified records based on the filter condition and load them into the destination table.

The reverse flow (SQL Server OLE DB Source to Dynamics CRM) can also be executed by following similar processes. A snapshot of the discussed data flows is shown below.

CData Sync

CData Sync is a tool that allows you to synchronize data between hundreds of data sources and destinations. With CData Sync, you can set up incremental load, which means that only new or updated data is transferred during each synchronization. This can help reduce the time and resources required to keep your data up to date.

Follow the given process for setting up CData Sync and implementing Incremental Load:

1. Start CData Sync Server.
2. Create two connections: Dynamics CRM as a source and SQL Server as a destination.
3. Create a new job for executing incremental load by adding the source and destination connections created previously. Set the replication type as “Standard.”
4. Add Tasks under the Job Settings option of the newly created job.
5. Select the table(s) (or entities) to be replicated from the source application (Dynamics CRM)
6. To set up an incremental load with CData Sync, specify a column from the Dynamics CRM entity that will be used to track changes to your data under the “General” tab of Task Settings. This can be done by selecting a column (integer or timestamp) in the Incremental Check Column dropdown (as shown here).
7. Run the table replication job. This will archive the selected table in the SQL Server from Dynamics CRM source entity.
8. After any changes to the data source, run the job again.
9. CData Sync then uses the Incremental Check Column to determine which rows of data have been added, modified, or deleted since the last replication and only transfers those changes to the destination.

CData Sync can be used to replicate data from a variety of data sources and applications, such as Dynamics 365 and Dynamics CRM to any on-premise or cloud database, data warehouse, or other data store, including SQL Server, SAP, MySQL, and Oracle.

Get Started with CData Today

Thanks to the CData SSIS Integration Suite and CData Sync, users can easily replicate Dynamics CRM to their SQL Server warehouses, providing simple alternatives to the deprecated Data Export Services.

Get started by downloading any of the SSIS Components, or start a free, 30-day trial of CData Sync. As always, let us know if you have any questions during your evaluation. Our world-class CData Support team is always available to help.