Use local SQL Server for data integration in D365 CE using SSIS and KingswaySoft

When working with Dynamics 365, data loading, transformation and cleansing tasks are frequent. Generally, these required transformations are simple and can be performed directly from the platform, but for more complex tasks, it may be useful to have a local database, in order to perform the data transformations using SQL syntax in the ETL processes.

In this article, we outline the steps required to install the free Microsoft SQL Server solution on a local machine (or a virtual machine in the cloud) and create a connection to the database for performing data loads into Dynamics 365 CE, using SQL Server Integration Services (SSIS) and KingswaySoft components.

As a starting point, it is necessary to install the free Microsoft SQL Server developer solution, which can be downloaded from the following link:

SQL Server Downloads | Microsoft

When executing the installation file, select the basic option, accept the terms and conditions and choose the installation directory.

Once the installation process has been completed, you will see, among other things, the data corresponding to the connection string and the directory from which to start the configuration wizard. Additionally, it is possible to install the SQL Server Management Studio tool (recommended), used for the connection and execution of queries on the database, which can be downloaded from the following link:

Download SQL Server Management Studio (SSMS) – SQL Server Management Studio (SSMS) | Microsoft Learn

At this point, the SQL Server instance has been successfully installed and enabled on the local machine.

Once the instance is configured, the next steps are to create the necessary databases according to the requirements of the ETL processes. To do this, a connection will be established through the SQL Server Management Studio tool, using the machine’s local user for authentication. As the server name, the name of the machine on which the SQL Server instance is installed is set by default.

After logging in, a new database is created in the instance from the object explorer.

The new database is named and the changes are saved, additional options and settings can be left with the default values.

If the process has been completed successfully, the new database will be displayed from the object explorer.

For the development of ETL processes, the following tools are required to be installed on the machine from which the processes are to be executed:

+ Visual Studio 2017 o 2019.
+ SQL Server Data Tools para Visual Studio (SSDT).
+ KingswaySoft SSIS Productivity Pack.
+ KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365.

NOTE: The use of KingswaySoft components for running integration packages from Visual Studio is free of charge, for deploying the packages to a SQL Server catalogue and automating them a license is required..

After installing the necessary components, proceed to the creation of a new Integration Services project from Visual Studio and configure the connection to the local database.

To do this, a new ADO.NET type connection is created from the connection manager and a new connection is created.

To access the databases created on the local SQL Server instance, the ‘. /’ value is specified as the server name, selecting the corresponding database in the drop-down below.

If the connection has been created successfully, a new entry will be displayed from the connection manager, allowing the connection to be used by SQL Integration Services components.

Finally, a use case applied to a specific real requirement is presented, in order to show the usefulness of having a local database for the implementation of the ETL process.

• Requirement: All accounts created in Dynamics CRM need to be updated with the actual end date of the most recent closed activity linked to the account itself, or to the main contact associated to the account, or to the lead associated to the account.

• Solution: We propose the implementation of an ETL process composed of the following parts:

1. Data extraction from CRM:: Activities, accounts, contacts and leads are retrieved from CRM, storing them in the corresponding tables created in the local database.

To do this, a data flow is created and, using the KingswaySoft Dynamics source component, the data corresponding to the following entities is obtained, selecting the necessary fields to perform the data crosses:

• Accounts
• Contacts
• Leads
• Activities

The following auxiliary tables are created in the local database, through the ADO NET Destination component (premium):

• D365_accounts
• D365_activities
• D365_contacts
• D365_leads

Field mapping between CRM data extractions and counterpart SQL tables is defined.

2.Data transformation Using SQL syntax, the tables fed with the CRM data during the previous phase are cross-referenced to retrieve the most recent activity by due date linked to each account.

• • Closed (inactive) activities linked to active accounts, contacts or leads of type email, appointment, phone call and task are crossed, sorting them by due date in descending order, and taking the first result by using the ROW_NUMBER SQL function.
• • Query:

• Result

3.Load data into CRM:: All accounts are updated in CRM by reporting the value corresponding to the most recent activity date calculated in the previous step.
To do this, an additional data flow is created in which the SQL query from the previous step is taken as the source and the CRM account update is taken as the destination through the Dynamics Destination component of KingswaySoft.

In the destination component, the field mapping corresponding to the account GUID and the calculated value of the most recent activity date is added.

Once the data flows corresponding to the 3 phases indicated have been implemented, it is possible to proceed with the execution of the same for the update of the accounts in CRM, thus solving the requirement raised in the use case.

In conclusion, throughout the article we have discussed the advantages of having a local database available for free for the execution of daily tasks related to data stored in Dynamics when more complex data transformations are required using SQL syntax, detailing the installation process and the resolution of a real use case.

I hope it will help you in your day-to-day work, either for the execution of data loads, regularizations or data cleansing tasks, or when you do not have a dedicated SQL server for the execution of specific ETL processes.