locked
MS CRM v4.0 import large number of accounts and contacts RRS feed

  • Question

  • I need to import 160,000+ accounts and contacts into my client's MS CRM v4.0 system.  The source file contains a mixture of accounts and contacts in a denormalized flat file; in addition to this, the import needs to identify existing accounts/contacts already in CRM and update those records accordingly.

    Since the standard MSCRM data import tool does not handle this, I created a custom SSIS package that utilizes a CRM.Proxy component to hanlde the web service calls.  Everything seems to work well in DEV when I run ~ 5000 records through it (DEV evivonrment SQL and CRMApp server are on a single server, PROD environment the SQL server and CRMApp are on separate servers).  However, when I schedule the SSIS job to run in production, it runs for a bit and then starts to throw errors (that I never received in dev):

    It thows a couple of these:

    System.Web.Services.Protocols.SoapException: Server was unable to process request.
       at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
       at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
       at CRM.Proxy.CrmSdk.CrmService.Create(BusinessEntity entity)

    Then it starts to throw network related errors:

    System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
       at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

    The above network error started happening after ~1 hr of the SSIS job running and once that error was hit, the import was basically failing for every subsequent record.

    This is the second weekend that I've tried to get this done and I've hit this wall.  At this point, I would just like to know what is the best method to import large record sets (100K+) of accounts/contacts/leads into MS CRM v4.0.  I searched the web for a while before I posted this but I can't seem to find any good write-ups on this particular issue.

    Any help or direction on this would be greatly appreciated!

    Anyone have any suggestions at all?


    • Edited by APJ (M1) Monday, July 23, 2012 7:32 PM
    Saturday, July 21, 2012 3:49 PM

Answers

  • Thanks for the advice.  I just wanted to close out the issue. I found the root cause of my issues.

    - The DNS name of the client's CRM server was not consistently resolving to the correct internal IP; this is what caused the network socket errors.  I modified the service request to use the internal IP which resolved this issue.

    - The 'server was unable to process' error was due to new CRM user accounts stored in the sale rep assignment table that were not assigned any security roles.  When the system would try to assign ownership of an account/contact to those new user accounts, it wourl result in a 'checksecprivelge' error.  Once I assigned security roles to the new user accounts, the issue was resolved.

    Monday, August 6, 2012 11:43 AM

All replies

  • hi,

    i think the errors are due to the timeout from application to the database.

    for small amount of records you can use import .

    but for large no of accounts you should use 3rd party tools such as scribe.


    If you see answers and helpful posts, please click Vote as Helpful, Propose as Answer, and/or Mark as answer._____________________________________________________Ahmed - CRM APPLICATION MANAGEMENT ______________________ These replies/answers are my own and doesn’t relate to any company or organization.

    Tuesday, July 24, 2012 1:28 PM
  • Hi Ahmed,

    Thanks for the reply. I'm going to try to just run the import in smaller batches.  I think the app server is just getting saturated with the service calls from the SSIS package and eventually its just shutting down the IIS worker process.  I sure wish MS would come up with a better solution for large data imports vs. relying on 3rd party tools.  Hopefully its gotten better in 2011.

    Thursday, July 26, 2012 12:44 PM
  • Hi,

    There's absolutely no need for a third party tool to import that amount of data in Microsoft Dynamics CRM 4.0 

    160 000 records is no big deal with minimal planning and tuning. 

    This guide will give you great insight: 

    Optimizing and Maintaining Microsoft Dynamics CRM 4.0: http://www.microsoft.com/en-ca/download/details.aspx?id=3997

    Hope this helps,

    Maxime

    Thursday, July 26, 2012 3:25 PM
  • Thanks for the advice.  I just wanted to close out the issue. I found the root cause of my issues.

    - The DNS name of the client's CRM server was not consistently resolving to the correct internal IP; this is what caused the network socket errors.  I modified the service request to use the internal IP which resolved this issue.

    - The 'server was unable to process' error was due to new CRM user accounts stored in the sale rep assignment table that were not assigned any security roles.  When the system would try to assign ownership of an account/contact to those new user accounts, it wourl result in a 'checksecprivelge' error.  Once I assigned security roles to the new user accounts, the issue was resolved.

    Monday, August 6, 2012 11:43 AM