Top 10 Data Migration Traps

How to avoid being caught by some common traps when migrating to Microsoft Dynamics CRM.

Considering the impact on delivery dates, testing, training, regulatory compliance and budget, it is always amazing how often the complexities of migration are overlooked. There are many traps to fall into when implementing your shiny new CRM system but migration is one stick that the underlying business will beat you with. Let’s look at 1o of these traps you want to avoid:

1. Your project plan has one task that says ‘Migrate Data’.

This is very easily done as either the team developing the new system or the vendor, can’t estimate the effort so it is just left as a single item. Don’t do it. Do some analysis that will at least give you a consideration of risk, effort and cost. Without these, managing the expectation of the business will be impossible.

2. Not using an automation tool to migrate data.

In the past we have conducted migrations based on SQL scripts, batch files, custom code etc. Quickly you realise that there are real benefits to automation. For example the new system has 5 new fields in a table. Rather than going back and redeveloping the migration code, you just point the tool (we like using Simego) at it and click update. 1 hour saved and no errors introduced, commit it to source control.

3. Migrating old data that might better be placed in a reporting system.

You should always consider that some data might be better placed in a reporting or document management solution. If you have a ‘end of year’ balance brought forward into a new system, do you need all of last years transactions or can you just lookup a report showing them? Rationalising some of this data can pay dividends in the scale of your migration project.

4. You let the vendor leave out migration or worse make it your entire problem.

Migrating data is risky and can be expensive; your development team knows how their current system works better than anyone. Make sure you have some migration commitment from the vendor before agreeing the deliverables and costs.

5. Assume that the migration with be straightforward because the old system and the new system do the same thing.

The architectural requirements of systems mean that they may have completely differing internal structures and security models. These factors influence the effort of migration by factors of 10. Even simple upgrades between versions can be the same as moving to a different vendor’s.

6. Underestimate the effect of the new security model on migration.

Security models are there to make sure that data is not viewed or manipulated by an unauthorised individual. During migrations it is often necessary to circumvent or assume the identity to extract and insert data. The developers of the current systems could make it extremely difficult for you to move data by using encryption, logic in the user screens and even more esoteric (smart at the time) tricks.

7. Thinking you can sort out the data quality once it is in the new system.

Often a driving reason the move to a new system is that it has better data quality control. For example, we often see telephone numbers saved as ‘TBC’ in an old system and the new system will only allow number to be saved. A significant phase in migration is cleaning the underlying system so that clean data can be stored easily into the new system. Often if you migrate dirty data, even with the best intentions, it loses priority and remains dirty undermining the value of your new system.

8. Underestimating the involvement from the business to clean the data.

As with 7, it is important to realise that users will have to assist in cleaning data. You can make this easier by creating lists of broken records and request rules for your automation tools to follow, but the bottom line is that your users know the data and are generally the best ones to clean it up most efficiently.

9. Not realizing the impact of month/quarter/year ends or other business events on the migration.

Many accounting systems require journaling and retrospective adjustment. Additionally, when picking a date for migration, make sure the business is available and not buried in month end, quarter end, tax year-end, year end or some other business event which requires significant business focus.

10. Not documenting the migration process so no one knows where the data came from!

When migrating data it is often necessary to transform the data several times before it finally resides in the new system. Often a client will ask, why is the address used and not the billing address from the old system? You need to have a method of tracing the source to target and often your automation tools can help to generate documentation.

At xRM Consultancy we are experts at migrating data from a variety of sources into Microsoft Dynamics CRM - talk to us to see how we can help you avoid these data migration traps


This post reproduced/adapted from an original by our friends at Simego

Migrating from ACT! to Dynamics CRM

Today let’s discuss migrating data from ACT! to Microsoft Dynamics CRM 2011. The last migration we undertook from Act to Microsoft Dynamics CRM also involved a brand new tool for us called Data Synchronisation Studio from the team at Simego. At xRM Consultancy we have dabbled with a lot of data migration tools, but Simego and it’s Data Synchronisation Studio stood out from the pack this time due to some powerful features it offers. So we decided to give this a trial to see if it really was worth investing our time and money in. For this post let’s pick one part of a migration that is often a pain point: attachments. This will also allow us to explore some of the features of Data Synchronisation Studio and how it stands up to the task.

Preparing the data

First question you may ask – what exactly is ACT! underneath the hood? More importantly, how do we extract that data and get it into Microsoft Dynamics CRM? ACT! sits on top of a SQL Server database so this makes querying and pulling the data out a lot easier than having to deal with APIs or flat files. Most of the data in here is quite straight forward and you’ll immediately identify some entity tables such as TBL_CONTACT and TBL_COMPANY. Linking the data up to related tables and retrieving the relevant columns is a little more challenging.  Our strategy to ease this pain is using a set of views and functions we’ve built up over time which allows us to easily extract the relevant and linked data.

For this example let’s deal more specifically with how attachments are stored and referenced. People already up to speed with Microsoft Dynamics CRM will know that attachments are base 64 encoded and stored directly in the database. In ACT! these files are stored externally and the file name is referenced within the relevant table – TBL_ATTACHMENT. So we need to combine queries with external files. Let’s start off with an example query:


Running will get you some results like this:


As we can see from the query ACT! gives us a list of files, which includes the file name, but no path. This isn’t a problem, because there will be a folder on the server where all these files are stored. Once you find them it’s pretty much a 1 to 1 mapping from each record to a file. All we need to do is map these files to the relevant entity records we will have already imported. Let’s take a look at a basic query to link these attachments to a company record. Take the following query:

      ,[c].[COMPANYID] as [OBJECTID]
      ,'account' as [OBJECTTYPE]
inner join TBL_HISTORY [h] on [h].[HISTORYID] = [A].[HISTORYID]
inner join TBL_HISTORYTYPE [t] on [t].[historytypeid] = [h].[historytypeid]
inner join TBL_COMPANY_HISTORY [ch] on [ch].[HISTORYID] = [h].[HISTORYID]
inner join TBL_COMPANY c on [c].[COMPANYID] = [ch].[COMPANYID]

Running this will get you some results like this:


The key thing to note here is linking to the company record via a table called TBL_HISTORY. This table is a multipurpose table, but it’s main purpose could be described as keeping a record of all “history” of actions that occur against records. I’ve included the TBL_HISTORYTYPE in the query to show how to pull out a description of the type of history activity that was recorded, but this isn’t something we need to migrate. Finally, and most importantly, we can use the link table TBL_COMPANYHISTORY to pull back records that are associated with companies.

Importing the data

Now that we have these company attachment records we need to import them. Remember, there is a mixture here between table records and files stored externally on the hard drive. This is the perfect opportunity to see what Data Synchronisation studio can offer in order to fulfil this task. At a first glance it looks just like your standard data import tool:


On the left you can link up your source data, i.e. the ACT! database. As I mentioned previously we have a set of views we create in the ACT! database so let’s hook into one of those. For all intense purposes let’s say the view is called xrmc_CompanyAttachments. On the right you link to your target table, which is the annotation entity in your Microsoft Dynamics CRM organisation. You can map across most of the columns straight from the query I prepared above.


So everything apart from the file contents has been mapped. Getting Data Synchronisation Studio to read this file and import it opens up a really interesting and powerful feature of this tool, namely Dynamic Columns. Every Dynamics CRM developer who has used a migration tool will love this – Dynamic Columns are simply properties exposed using C# classes! So let’s write our property:

// Convert the pysical file to a Base64 string
public string FileContents
    string fullFilePath = "C:\\Path To Act Attachments\\" + FILENAME;
    if (System.IO.File.Exists(fullFilePath)) {
      return Convert.ToBase64String(System.IO.File.ReadAllBytes(fullFilePath));
    return string.Empty;

And you end up with the following in Data Synchronisation Studio:


Imagine the power of this. Suddenly, reading in a file becomes such a simple task. Simply build the code using the provided build button and this provides you with a new list to map to your data. Map this new Dynamic Column across to your DocumentBody and you’re pretty much done:


If we do a compare, using the Compare A -> B button, let’s take a quick look at what our data looks like:


And that’s pretty much it. Click on Synchronise and watch those file migrate!

Next time I’ll discuss a more advanced scenario where we had to merge customer data from ACT! and Sage 50 Accounts into a single view of the customer within Dynamics CRM.

If you’ve got ACT! and you are looking to upgrade to Microsoft Dynamics CRM why not get in touch with our team.