Monday, February 12, 2007

Import Mass Data to CRM 3.0: Unable to connect to the remote server

If you have mass/bulk data to be imported which is a custom entity in Microsoft CRM 3.0, there is no other better way, rather than built your own application. By default, CRM 3.0 only provide Bulk Import for Account, Contact and other several system entities (if any). Good luck if you have a lot of custom entities that is not possible to use built-in Bulk Import.

And I am not lucky that my project has quite a lot variety of data to be imported to CRM which is a custom entity. So, I have to built my own application to mass import the data. The application I have built have several basic function, like mapping between excel column and CRM attribute, lookup entity through different attribute, some validation, save/load settings, etc. If I got time, I hope I can tell more about this application in next post. After all seems ok, time to run the real data migration comes. And another unlucky thing happens that after importing about 1000+ records, CRM seems rejecting connection. The specific error is: "The underlying connection was closed: Unable to connect to the remote server". So, no matter what, I have to find a way to solve this problem. It's impossible for me to import every 1000 records for 1.5 million records. First, I try to set some thread sleeping time, or delaying inserting record for several second after several records, no luck. Second, I try to reset the connection by reinitializing the CRM Service, no luck. At last, I ask my uncle Google, and find really good and relevant Knowledge Base, the almighty "913515". If you are not Microsoft Partner, you won't be able to access this, but I think, if you develop Microsoft CRM, you are a Microsoft Partner. But, the point is to set the registry for MaxUserPort and set TcpTimedWaitDelay in HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters (they are new registry key value). The maximum value for MaxUserPort is 65,534 (recommended 30,000) and TcpTimedWaitDelay is 260. If you face the same problem and unable to solve it, just contact me. :)