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.)