SSIS KingswaySoft - Multi-threading errors

Nick Chin, 11 June 2019

With the release of KingswaySoft v9.0 in July 2017, they added support for multi-threaded writing in the CRM destination component. When multi-threading is enabled you can set the number of threads to run simultaneously writing to CRM.

I took over a project where the Batch Size was set to 200 and the Thread number was 30. When running the SSIS Packages there was a significant amount of errors due to server timeouts and the performance was very slow.

Upon reading the documentation I realised I needed to adjust the setting to a more suitable limit.

Batch Size

For a single thread it is recommended to 250 or lower to avoid timeout errors.  When using multi-threading is used it is recommended to use 100.

The Dynamics 365 Online API may have throttling, which prohibits more than 2 concurrent ExecuteMultipleRequest executions per CRM Organization. Therefore it is not recommended to run more than two CRM destination components in parallel to write to CRM Online.

Note: the throttling limit may be lifted by raising a ticket with Microsoft support and require approval. KingswaySoft now says this has been reportedly removed as of April 2019.

Threads

While there is a maximum number of 100 threads, it is recommended that the number of threads are 20 or lower to avoid server errors, but this depends on the server.

The default number of threads is 16.

For on-premise the latency is a lot less and it can generally handle 20-40 threads.

Entities

Some entities will have a greater load on the server to create or update.

For Notes with Attachments (annotation) or Email Attachments (activititymimeattachment) it is recommended to use a batch size of 1 with no multi-threading due to server load. Some large files when uploading online can have timeouts or cause out of memory problems. Dynamics 365 shouldn’t be used to store a lot of large files, so consider using SharePoint or file storage like OneDrive.

In summary the settings can vary greatly depending on performance, this is influenced by whether you are online or on-premise, the Server/device running SSIS and the type of entity. Also using SSIS development best practices helps to improve the performance.

Since I was running the SSIS Packages off a laptop I needed to drop the setting down to a Batch Size of 50 and Thread to 16 for most entities.