The other half story about BDD, SSIS, and CRM

[UPDATE - July 7, 2017] In our most recent v9 release, we have added the support of multi-threaded writing in our CRM destination component, so this blog post is no longer useful. You can simply turn on multi-threaded writing in the destination component to achieve the same (with much greater flexibility or even better performance). [/UPDATE]

[NOTE - May 5, 2016] The config file changes are no longer necessary since our v7.1 release on May 5, 2016. The technique described in this blog post is still useful if you are using an older version or even if you may be using a different approach for data migration or integration.

This is an update of my previous blog post about using BDD component in SSIS to improve the performance of loading data into Microsoft Dynamics CRM.

I was only able to tell the half story of the technique in the previous blog post. Here is the other half.

In order to make full use of the BDD component in SSIS data flow, you need to increase the connection limit that is imposed by Microsoft .NET framework, which is a maximum of 2 connections per host (e.g. server). In order to overwrite this limit, you need to modify DTExec.exe.config and DtsDebugHost.exe.config files under DTS\binn folder by adding the following connectionManagement section.

<configuration>
 ...
 <system.net>
   <connectionManagement>
     <add address="*" maxconnection="100"/>
   </connectionManagement>
 </system.net>
</configuration>

The above configuration allows up to 100 connections per host at the same time. You may change the number based on your needs. Note that if you are using 64-bit system, you will need to make changes to the files under both Program Files and Program Files (x86) folders. 

After making the above changes, I observed more performance improvement in my SSIS data flow. I was able to load 0.9 millions of records into CRM within one hour (5 outputs and 10 outputs had almost identical performance benchmark, while 5 outputs outperformed 10 outputs a bit in my test). 

This has been reflected in our product FAQ page. 

If you write custom code to load data into CRM using multi-threading, you should make similar changes to your application's config file as well. 

It should also be noted, if your SSIS data flow reads or writes data simultaneously to a single host using web service interface, you should consider making this change, regardless it is WCF, WSDL service, or maybe even a REST service. 

This is a tip that I learned from CRM project manager Mahesh Hariharan at xRM Acceleration Lab during the week of Apr 2 to Apr 6, which was held at Microsoft campus in Redmond. 

BTW, in case you don't know, Microsoft has an xRM Acceleration Lab program which is open to Microsoft Dynamics CRM partners to help them build up the necessary technical skills to bring your solutions faster to the market. The lab is a one-week one at Microsoft campus, which consists of a number of technical sessions presented by CRM MVP folks and some product team members. The best part about the lab is, your team resources will be working with a gang of MVP folks (and some product team members) side-by-side to develop solutions during the week. This is a very efficient way to build up the technical skills by building something real on top of Microsoft Dynamics CRM platform. 

Hope this helps.