Using SSIS 2008 to migrate data into Microsoft Dynamics CRM

Question

I've recently been toying with data migration into Microsoft Dynamics CRM using MS SQL Server Integration Services. First, the basic problem domain:

I have an exported flat file from a previous homebrew CRM system, the goal is to efficiently cleanup the data, and then to move the data over into Dynamics CRM. I've decided to put in one entity at a time in order to keep the orchestrations simple. There is currently an Attribute in CRM that contains the primary key we used in the old CRM. The basic process in my head currently is, import the flat-file into SSIS using the Excel Adapter, then make a connection to the Microsoft Dynamics Database in order to Query for data related to the import. Since I'm not updating the database in anyway, I figure this is fine. Once I have my list of Account Guids and Foreign Keys, I will then compare the list of Excel rows to the list from the CRM database, and create a new derived column with the GUID in it indicating that the operation should be an update, and that the guid to use is the one in that row.

I then create a script object, and make a call out to the CRM Web Service, I go down the Excel file Row by Row, and if it's has a value in the derived column, it updates the CRM, else it just creates a new entity.

If all goes well I'll package the SSIS and execute it from the SQL server.

Is there any gaping flaw in this logic? I'm sure there are ways to make it faster, but I can't think of any that would make a drastic difference. Any thoughts?

Answer 1

Sounds good to me - by getting the GUIDs directly from the database, you are are reducing the number of necessary web service calls.

Answer 2

Your design is good. Actually, specialized CRM integration software Scribe (and probably others too) do this very much this way with most of their adapters. They use direct database access for reads and calling web service for insert/update/delete and other operations.

I just wonder if this complication is actually necessary. It depends on the size of the data you have to import. I usually deal with data that gets imported over one night.

Answer 3

CozyRoc has recently released a new version, which includes Dynamics CRM integration components. Check the official release announcement here.