Migrating GoldMine to Microsoft CRM 3.0:
Phase 0: Initialization/Groundwork

General Setup

First you will need to set up your CRM database. Use a seperate user in the Microsoft CRM system to keep track of what was migrated and what was added manually. This will enable you to run the migration multiple times without the problem of creating duplicate records.
Next create a seperate user on the SQL Server and give this user exactly the privileges necessary for migration. Read acces to the Goldmine, write access to the Microsoft CRM database and the rights to create a new database on the server should suffice. This will save you from possible mistakes that could corrupt your source and/or unrelated data.

Get the Code

OK, so before we digg into to code itself, go and get the project along with the SQLTree program. Open it and load the source .stp project file into SQLTree so you can follow along with this guide, beacuse I am not going to post all the code here. There is way too much of it (over 4000 Lines) and it would just make everything really unreadable.

Initialization

This section will cover the first set of actions (Initialize) in the project needed to migrate Goldmine data to Microsoft Dynamics CRM 3.0. Initialization performs three main tasks that we will call: "Staging Database", "Mapping Tables" and "Conversion Functions".

The Staging Database

In order to make the copy process clean and also keep an association with the original data, you will need an intermediate or "staging" database. The data is first copied and tranformed to this staging database before it eventually gets copied into the Microsoft CRM database. This way you can also perform statistical analasis and intergity checks on the data before it finally ends up in the CRM itself.
So, here you go:

You should now have four databases on your server (besides the usual system databases):
The first three are the ones you will be working with. The last one (<crmname>_METABASE) is where Microsoft CRM stores all its meta and customization information. Make sure you back both this and the <crmname>_MSCRM database up. Always backup and restore them in pairs. Failure to do so can cause some really baffling errors. My recomandation is to this by detaching them, making a copy of the data files and then reattaching them. You may need to stop the Microsoft CRM server in order to be able do this.

Mapping Tables

Unlike the Data Migration Franework, Microsoft CRM Primary Keys are all SQL uniqueindentifier types, commonly called "Global Unique Identifiers" (GUIDs). To create one in SQL use the NEWID() function.
Goldmine on the other hand uses obscure character strings for their Primary Keys. In order be able to map the source Goldmine records with the new target records in the Microsoft CRM system, we use a mapping table that stores the associations between them. We will call this table GMCRMMap.

Here is the create table SQL code: 
CREATE TABLE GMCRMMap
(
    ContactId uniqueidentifier,
    AccountId uniqueidentifier,
    Id int IDENTITY(1,1) NOT NULL,
    ACCOUNTNO varchar(20),
    RECID varchar(20),
    Contact nvarchar(100),
    FirstName nvarchar(50),
    MiddleName nvarchar(50),
    LastName nvarchar(50),
    FullName nvarchar(153),
    OwningUserName nvarchar(20),
    IsValid int
);

The first two Fields are for the new Contact and Account IDs. The Id field serves as a incrementor for debugging purposes. ACCOUNTNO AND RECID contain the link to the Goldmine data. The Contact field will contain a merged first and Last name used for linking purposes later on. FirstName, MiddleName, LastName and FullName should be self explanatory. OwningUserName currently does nothing, but could be used to include the ownership of the migrated records sometime in the future. Currently all records are assigned to a single user (called Mr. Migrator) identified by the {MigratorUserId} global variable.

Views

In order to make accessing the data easier, we also create three views: ContactsMap1, ContactsMapFull and ContactsMapEx. Basically these present a joined view of the GMCRMMap table to the Goldmine CONTACT1 and CONTACT2 tables. This cuts down on the length of those INNER JOIN statements.

Conversion Functions

Many fields in the Goldmine database need to be converted to their cleaner Micrsoft CRM equivalents. We create SQL functions in order to accomplish this:



Copyright © Jonathan Gill 2007