Data Migration Part I

Getting your data into your CRM system is one of those problems people don’t think about when planning their CRM project either because they are concentrating on the features and how they are going to use their new CRM system or because they just do not realise what is involved.

Data Migration can be tricky for a number of reasons, such as:

  • multiple data sources which need to be merged which can lead to difficulty matching and end up with duplicate data
  • existing systems do not have export or full export functions so getting the data can be difficult
  • the structure of the data in the existing systems does not match the CRM target structure
  • individual fields on existing systems do not have equivalent fields on CRM e.g. you may have a free text field on the legacy system but are planning a picklist for the dame data on CRM 
  • the quality of the data – a lot of legacy systems do not enforce data integrity which makes the migration process difficult
  • misuse of data fields – users will use fields for different purposes and it is not uncommon to find "codings" embedded in fields especially where the legacy system is hard to customise

There issues mean that data migration can take 30-40% of your CRM project budget and it you haven’t allowed for it then you will miss your implementation date and having a system without data is far from useful.

Data Migration can be hard if you just dive in without planning and working what you need to do.  By following a well defined process you are more likely to success in getting your data into CRM ready for go-live. There is a generic method for data migration with three stages known as ETL which breaks the data migration down into separate problems

  • Extract – getting the data out of the legacy systems
  • Transform – this is the conversion of the data from its legacy structures into the structure CRM expects
  • Load – this is the actual process of importing the data into CRM

[You can add data cleansing into the above but I find that cleansing tends to be performed in one or all stages depending on the situation]

In the Extract stage you need to get the data in a neutral format e.g., Excel, Text file or Access. How you do this depends on the legacy system. Some have export functions but beware that some of these exports do not export all the data you need e.g. in ACT! the export only covers groups and contacts not activities and opportunities. So you may need an alternative approach and which may require a third party utility all of which adds to the costs.

In the Transform stage you need to bring together all the data sources and create data that "looks like" CRM i.e. Accounts, Contacts, Activities, Opportunities, Orders etc. Generally this requires processing and logic and either a specific program needs to be developed or a tool used.

In the Load stage you need to use CRM’s functions to import the data into the CRM database.

Microsoft CRM provides four methods for importing data:

  • Within CRM you can import Leads and Contacts from Excel – this is of limited use in data migration as often there is more data needed. This function is more aimed at importing lists of people you have either collected at trade shows or have purchased from a commercial list
  • Within CRM you can import Contacts from Outlook – this is useful where the only data source is Outlook but again you will not be able to import tasks and will have to manually create Accounts as these are not created from the contacts.
  • SDK – Microsoft provides an API for creating (and updating) data in CRM. You could use this to create the records in CRM using software you
  • Data Migration Framework – a set of tools and documentation provided for importing data into CRM

What we have migrated into CRM using the above approach:

  • ACT!
  • Goldmine
  • Sage Line 50
  • Bespoke solutions based on SQL, Superbase, Access, Filemaker and Excel

Part II of this document will go into the migration process in more detail, tell you what tools we use in each of the stages and explain the features of Microsoft CRM Data Migration Framework.

Advertisements

About Julian Sharp

I have worked with Dynamics CRM since 2004 and have been involved in over 100 CRM projects. I am a Dynamics MCT and teach Dynamics 365 and Azure in the UK and across Europe
This entry was posted in MSCRM. Bookmark the permalink.

6 Responses to Data Migration Part I

  1. Moonis says:

    No source on internet specifies that how we can 1st export data out of ACT. in my case i need only contacts, activities and notes/historyall other are theoratical things. practically above step is missing

  2. Nick says:

    Where is Part 2? I’m very interested to see how to tackle the full process.

  3. Janine says:

    Is there a Part II to this document yet? I need help in migrating data from ACT! to CRM.
     
    Thank you

  4. Julian says:

    There was a part II but it never was published and now with 3.0 DMF being available I think it is time for a followup and I will cover migrating from ACT, Goldmine and other systems

  5. Craig says:

    Did you create a migration document? I have to migrate Act6 to CRM 3.0 and would love to see a step by step guide or something similar of the process required.

  6. עדי says:

    Hi,
    You can use SQL Server Integration Services (SSIS) to load data into the CRM. I believe this is the best way of doing that.
    I made a tool that helps generates the DLL required to use from SSIS script component to write Microsoft SDK code. You can download this too for free from this location http://www.ssis4crm.com
    You will fined there intsractions of doing a simple load.
    This way is supported by Microsoft (using MSCRM SDK)
     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.