Data Integration and Data Migration has always been one of the most challenging tasks in any Dynamics 365 implementation. The most common and popular method used within the community is through SQL Server Integration Services (SSIS) along with a third party connector/toolkit from KingswaySoft.

While this is the most popular method, you can sometimes encounter performance issues during the implementation. In this blog, we will show you how to tackle performance issues by optimizing the configuration.

Optimizing Multiple Connection Settings

It's no secret that utilizing ExecuteMultipleRequest (which was introduced when Dynamics CRM 2011 UR12 was released) can drastically improve the performance for bulk data load. Instead of using a single connection to create or update a single record, ExecuteMultipleRequest allows you to create or update more than one record per connection.

By default, the platform only accepts up to two concurrent connections and each connection only accepts up to 1000 records. In some cases, dependent on the type of contract or license you have with Microsoft, you may be able to request for additional connections. If you exceed the number of concurrent connection available, the platform will reject the call and return an error message indicating that the server is busy. Fortunately, ExecuteMultipleRequest is built-in to KingwaysSoft and we just have to set the optimal settings. So what is the best settings?

In KingswaySoft, ExecuteMultipleRequest is indicated by following settings:

1. Number of concurrent connection: Use up to X thread in total

2. Number of records per connection (batch): Batch Size

In the example above, you will use five concurrent ExecuteMultipleRequest connections with a batch size of 30 records. While in theory the maximum batch size is 1000, in real world scenario, you will want to keep the number between 50-100 to maintain performance and stability and avoid timeout. Ideally, you will want the number of connections to be lower than the maximum connections your environment has, since other integrations or migration jobs may be running at the same time and sharing the number of connections with your SSIS job.

Optimizing .NET Client Settings

Although, the SSIS Job has its own connection settings, it is running on top of .NET framework and therefore will be limited by the .NET maximum number of connections settings. This particular setting determines the number of open connections the SSIS Job is going to establish and it may throttle the overall SSIS job performance. You can remove this limitation by adding the following configuration section:

      address = "*"
maxconnection = "100" />

Add this to your machine.config file located in: C:WindowsMicrosoft.NETFramework64v4.0.30319Config and C:WindowsMicrosoft.NETFrameworkv4.0.30319Config

This method should help optimize your configuration. For more Dynamics 365 tips – check out our blog!

Happy Dynamics 365'ing!