[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

How to continuously update Microsoft Dynamics CRM from a external MS SQL Server 2000

What will be the best way to extract data from MS SQL Server 2000 and update CRM in a daily schedule?
0
tommym121
Asked:
tommym121
  • 3
  • 3
2 Solutions
 
Chinmay PatelEnterprise ArchitectCommented:
Hi tommym121,

There are multiple ways to do so as I mentioned earlier for SQL server, similar to that we have for CRM as well. As CRM itself runs SQL in the background.
1. SSIS
2. Scheduled SQL jobs
3. .Net service or a command line application

When you are updatind CRM's data you must make sure that these fields are not used by CRM for other business process/es.
For example, when you update CRM record there are few other fields which will be automatically updated[for example date modified, modiefied by etc] if that update is done via CRM. If you directly update some data in SQL and bypass CRM's platform layer, it will not update those fields and you will not be able to capture data accurately.

Regards,
Chinmay.
0
 
tommym121Author Commented:
Chinmay,
I understand I should use a Web service to update CRM entity.  How do I extract data from SQL sever 2000.  Can I use SSIS?  Is SSIS compatible with SQL Server 2000 since it introduce in SQL 2005
0
 
Chinmay PatelEnterprise ArchitectCommented:
Hi tommym121,

SSIS was available with SQL 2000 as well with the name of DTS and yes you can use SSIS to load data from SQL 2000. Just make sure that if you are on a 64-bit environment you do a PoC upfront. I have heard that there are some compatibility issues with 64 bit version of SSIS and SQL 2000.

Regards,
Chinmay.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tommym121Author Commented:
Can you use SSIS of SQL Server 2008 R2 on SQL 2000 or you need to use DTS instead.
0
 
Chinmay PatelEnterprise ArchitectCommented:
No. If you want to develop the package on SQL 2000 only way is to use DTS and then if you need to use same package of DTS on 2008R2 SSIS, you can do so. But there is no reverse compatibility i.e. if you develop a package in SSIS you cant convert it to a DTS package.
0
 
tommym121Author Commented:
Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now