Best practice to constantly update data to CRM from an external data source.

(0) Share

Report

Posted on by Community Member

Hi,

I have a CRM 2013 organisation that holds Property data that is maintained by an external application (WinForms with MS SQL). Any changes made to the Property data in this external application need to be synced with the same data held in CRM. This does not have to be real time and can quite happily happen overnight each day. Changes can be in the form of properties deleted, updated or new properties added. The update will always happen from the external application to CRM and never the other way around. In CRM, records created in CRM will have references to Properties (that could possibly be deleted by the external application).

I can setup triggers on the required fields in the database of the external applications so that any property records that are changed, will get added to a separate Sync table which will have all the property fields along with an Action field with values I (Insert), U (Update) and D (Delete).

My question is what is the best way of getting this data (insert, update, deletions) from the Sync table into CRM in an automated fashion. As there are multiple ways of doing this I wanted to know, which way was preferred and why. Any help will be much appreciated. Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

ashlega 34,477 on at

Like (0)

Report

RE: Best practice to constantly update data to CRM from an external data source.

Hey,

you can use it in a few ways.

1. If you are a developer or have dev resources

You can create a custom application. For something simple like that, maybe I would rather do this. In terms of maintenance, it's not a lot (but you do need to always have a developer around if / when something changes in the process)

2. If you are not a developer, you have to use a tool like Scribe or SSIS

Assuming you have SQL, you already have SSIS, so you just need an SSIS connector. There are few good ones - KingswaySoft, CozyRock, etc. I blogged about it a couple of months ago:

www.itaintboring.com/.../dynamics-crm-data-migration-with-ssis-overview

You'll have to purchase a Dynamics connector license from one of those companies since you'll have to schedule those SSIS packages.

As for Scribe.. it should also work, but licensing may end up being more expensive.

Was this reply helpful? Yes No
Verified answer

Thomas David Dayman 11,323 on at

Like (0)

Report

RE: Best practice to constantly update data to CRM from an external data source.

We are doing the same thing in our company. I would also suggest KingswaySoft.

If you are already familiar with both SQL and CRM then using it will be a breeze.

You would need to download:

Sql Server Data Tools: docs.microsoft.com/.../download-sql-server-data-tools-ssdt

KingswaySoftAdapter: www.kingswaysoft.com/.../download

You can also Upsert Data to CRM which is really good if you are looking create data but do not want to produce duplicates in the process.

Was this reply helpful? Yes No
Suggested answer

Andreas Cieslik 9,267 on at

Like (0)

Report

RE: Best practice to constantly update data to CRM from an external data source.

With an SSIS adapter or tool like from Kingsoft this could be done in a supported way:

www.kingswaysoft.com/.../ssis-integration-toolkit-for-microsoft-dynamics-crm

Was this reply helpful? Yes No