Migrating Act! to CRM with the new CRM Data Migration Framework

We just completed a data migration from Act! 6.0 to Microsoft CRM 3.0. Rather than use a third party migration tool like Scribe we decided to use the recently released Microsoft CRM Data Migration Framework (DMF).

The primary reason we opted to use the DMF was that the data in Act was very “dirty”. Specifically each record in Act might actually represent anywhere from 1 to 5 individuals with the names and contact information for the secondary “individuals” on each record separated by various delimiters. I don’t know that Scribe couldn’t do it but I had a feeling it would be easier to script out the migration in SQL than it would be to figure out how to make a tool deal with these irregularities. 

The overall process was exporting all the data out of Act! 6.0 and moving it to a format that was more SQL Server friendly. We decided to use a tool specifically designed for the purpose Import Pro from crmaddons.com http://www.crmaddons.com/pc-15-3-export-pro.aspx. From Access we moved the data to a new SQL Server database (ActData) using DTS. 

The DMF consists of several wizards and a common data framework (CDF) database. The wizards assist in mapping data in the CDF to data in CRM to account for various pick list option values, record ownership mapping, etc. Getting clean data into the CDF is the key to a good migration with the DMF. 

The majority of our effort was creating and testing scripts that normalized the data, split out the secondary contacts in each contact record and then migrated the data to the CDF database. Once the data was in CDF things went smoothly with a few exceptions.  

First, we had problems importing historical data as completed activities. It appears that the DMF doesn’t allow for the creation of a record with a statecode of 1 (completed.)  We worked around this issue by importing the activities in an open state (statecode=0) and then ran a script in the MSCRM database to update the statecode and statuscode to mark the activity completed.  

Second, Act stores dates and times without time zone correction and CRM stores dates and time in GMT. For our conversion this resulted in some records showing the due date as one day earlier in CRM than it was in Act. We corrected this by modifying the scripts to add 8 hours to all dates as we moved records to the CDF database.  

Third, we originally had a lot of activity records that would not import on our first test import. Analysis of the failures indicated that they had only one thing in common; a date field where the time was exactly midnight (00:00:00). This was evident when looking at the DMF logs that contain the XML used to insert the record in CRM. When the time was exactly midnight, the time part of the date information was not in the XML field and CRM is very particular about using proper UTC form on date/time information. The workaround here was to add 8 hours and 1 second to all the dates as we moved records to the CDF database. 

Once all of the scripts were done and tested we were able to complete a test migration on our development environment and then run the production data migration. The Data Migration Framework is a great tool but it requires a fair amount of SQL scripting knowledge. Moving data from Act to the CDF database was relatively quick once the scripts were developed but the migration from the CDF database to MSCRM was relatively slow (3 to 4 hours to move 65,000 records.)

8 thoughts on “Migrating Act! to CRM with the new CRM Data Migration Framework

  1. The company I work for is migrating from Goldmine to MS CRM 3. How well does this migration tool work with that?

  2. Do you mind me asking about how long this solution took to implement, from start to finish? I am estimating a similar effort and am trying to get a feel for the work involved. Thank you!

  3. With the Import Pro application, did you manage to Export 100% of the data out of ACT!6? Doesnt it take out attachments as well? How much data can be taken out?

  4. Scott

    I need to undertake exactly the same process.

    Although I am familiar with Access, SQL, VB etc. the CRM DMF is all very new to me.

    It would be a really big help if you were able to let me have a copy of your code so help me on the way.

    If you were able to do this I would really appreciated it.

    You can contact me at:

    roger -AT- thelightfoots.co.uk

    Thanks and Regards

    Roger Lightfoot

Leave a Reply to Josh Painter Cancel reply

Your email address will not be published. Required fields are marked *