Answered by:
SQL to CRM

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
-
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
Scott Durow
Blog www.develop1.netFollow Me
Rockstar365
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"- Proposed as answer by Scott Durow (MVP)MVP, Editor Monday, June 24, 2013 9:44 AM
- Marked as answer by Pete Newman Tuesday, June 25, 2013 7:42 AM
Monday, June 24, 2013 8:21 AMAnswerer
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 SinayMonday, 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
Scott Durow
Blog www.develop1.netFollow Me
Rockstar365
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"- Proposed as answer by Scott Durow (MVP)MVP, Editor Monday, June 24, 2013 9:44 AM
- Marked as answer by Pete Newman Tuesday, June 25, 2013 7:42 AM
Monday, June 24, 2013 8:21 AMAnswerer -
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