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:

SELECT * FROM TBL_ATTACHMENT

Running will get you some results like this:

SS1

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:

SELECT [a].[ATTACHMENTID]
      ,[t].[NAME]
      ,[a].[FILENAME]
      ,[a].[DISPLAYNAME]
      ,[c].[COMPANYID] as [OBJECTID]
      ,'account' as [OBJECTTYPE]
from TBL_ATTACHMENT [a]
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:

SS2

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:

ss3

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.

ss4

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
{
  get
  {
    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:

ss5

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:

ss6

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

ss7

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.