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?
tommym121Asked:
Who is Participating?
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.