Migrating GoldMine to Microsoft CRM 3.0
Phase 1: Linkage/Preparation

Overview

This section will cover the second set of actions (Link) in the project needed to migrate Goldmine data to Microsoft Dynamics CRM 3.0. The aim is to create accurate links between the Goldmine records and the soon to be Dynamics CRM records. Also some data needs to be reformatted to fit the new system.

Filling GMCRMMap

First up GMCRMMap needs to be filled with data. This is accomplished by a SELECT INTO of the ACCOUNTNO and RECID from from the CONTACT1 Table and adding a NEWID() for both the ContactId and AccountId.

INSERT INTO GMCRMMap
(
    AccountId,
    ContactId,
    ACCOUNTNO,
    RECID,
    IsValid
)
SELECT {LimitRecordSQL}
    NEWID() AS AccountId,
    NEWID() AS ContactId,
    ACCOUNTNO,
    RECID,
    0 AS IsValid
FROM
    {SourceDB}.dbo.CONTACT1;

Filling GMCRMMap from CONTACT1

The {LimitrecordSQL} placeholder is where the global variable of the same name is put to limit the number of records copied/transfered. As the rest of the code in this project relies on this linkage table for its references, this determines how many Contacts and/or Accounts are migrated.

Constructing Name Parts

Goldmine stores its contacts full name and last name but not its first name. So, in oder to get the contacts first name we will have to extract it. This is what we do in "Construct Name Parts". The last name is "subtracted" from the full name and the remainder is saved as the first name. In order to get the spacing right a "middle name" is introduced. This field acts as a sperator, containing a space character for those conacts that have a first name and an empty string for those that don't.
Finally the full name is then re-constructed from the seperate name parts.

Correcting Faulty Names

While migrating the data from Goldmine we discovered that some last names did not occur in their full name counterparts. The reason was that those names were truncated. Apparently Goldmine could not handle names above something like 20 characters. So we queried for those records, extracted and pieced together their correct names and hard-coded/scripted them into this SQL script action.

In this generic version of the SQLTree project this action contains 4 template SQL statements that you can fill in with your own renegade names. It is disabled by default in order not to thow an error, so you will have to re-activate it before it will work.

Managing Valid Records

To finish this section/phase off, in "Include Companies as Vaild" all records that have accounts (in Goldmine they are called Companies) associated with them are marked as Vaild (their IsValid flag is set to 1) and then in "Delete Invalid Records" all non marked records are removed. Most of these are nosense records that just contain "junk" data, but you may want to check first using

SELECT
    *
FROM
    GMCRMMap
WHERE
    IsValid = 0;

Contact Ownership

This was an attempt to migrate the ownership of the contacts, but was abandoned early on as not necessary. I left it in as a deactivated action in case by any chance it was needed sometime in the future. Its basically just a stub of old code that could act as a starting point if so required.