locked
SQL to CRM RRS feed

  • Question

  • I have spent weeks trying to work out how to do this and always hit a dead end. I'm no expert in crm or programming, but I'm sure I'm not trying to do something 'that' unusual, and some where there be a tutorial

    Scenario

    Windows 2008R2 server hosting Windows SQL 2008R2 server that our clients connect to via a web App

    Windows 2008R2 server hosting CRM2011 and SQL server 2008R2

    I am trying to develop our crm but need to ensure that the data in the crm system reflects that in the SQL server.

    I have tried to use SSIS fired from a trigger but can't get that to work, so am turning to the forum for advice and suggestions

    we are looking to try and bring CRM in as a phased migration so I need to have something updating crm from SQL in real-time


    Dont ask me .. i dont know

    Sunday, June 23, 2013 2:29 PM

Answers

All replies

  • HI Pete

    If you want to use SSIS I recommend using the SSIS Integration Toolkit for Microsoft Dynamics CRM you can download from here http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm


    Regards,
    Damian Sinay

    Monday, June 24, 2013 5:49 AM
  • Hi,

    Theoretically you can create a SQL CLR assembly that calls the CRM Webservices - but I'm not sure it would be a good idea if you've got performance requirements of your SQL database:

    http://pogo69.wordpress.com/2011/05/10/crm-web-services-from-sql-server-transact-sql-sqlclr/

    You might be better off using change data capture and a batch program to do the sync: http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

    hth


    Scott Durow
    Blog www.develop1.net    Follow Me
    Rockstar365
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Monday, June 24, 2013 8:21 AM
    Answerer
  • I would look as using Inaport see Inaplex though things like Scribe can also probably do it. In Inaport you would set up a profile to read changed records in your portal (assuming it has a modified date field) and update/create in CRM. You might also need a profile to delete records which have been deleted in the portal. With Inaport you can make the profiles read from the SQL with a cutoff datetime which is updated after each run. The profile can then run nearly continuously as each run should take seconds. 

    A couple of other points:

    1. You can make it two way by having a profile to read from CRM  write to SQL again with a cutoff date. Obviously there is a risk an update in both systems could be lost but if the time taken for each profile is small this is less likely.

    2. Eventually I presume you would move all data to CRM and avoid data sync which is best practice and avoids simultaneous updates issues  


    Paul Developer of Summary Plus for Dynamics CRM. http://www.clew-consulting.com/summaryplus

    Tuesday, June 25, 2013 5:58 AM