Mar 11 2005
Migrating GoldMine to Microsoft CRM
Posted in
Forenoon watch, 5 bells (10:56 am)

Migrating From GoldMine 2000 to Microsoft CRM 1.2

The purpose of this document is to assist people that wish to migrate customer data from GoldMine 2000 using Microsoft SQL Server to a Microsoft CRM v1.2 implementation

About Importing Data

The data import process is not a difficult one, but it can be tedious and problematic if you don't know what you're doing. I spent many hours refining this process and tailoring it to our specific customer database schema, so these pages may not always apply to you, but should provide general steps to get you started on your migration path. I started from scratch, and didn't have much to go on, which is why I decided to write these documents to help the next people. I spent about six months writing custom applications and data transformation packages, importing test data, verifying imported data, and so forth. Don't expect that you can get everything in a couple of days. You will almost certainly require several weeks to develop, test, and verify that your data is all imported, and imported properly.

You can't just use the Data Transformation Services in MS SQL to copy your data from GoldMine to CRM—it doesn't work that way. When you install the Data Migration Framework from the MSCRM CDs, it creates and intermediate database which you can export your CRM customizations to. You can then export your data from GoldMine into this temporary database, clean up the data, and then migrate it into CRM. Microsoft has provided several tools that will help you clean, verify, and import your data. They even have spreadsheets that provide useful information about importing your data, but make sure you check Microsoft's website for newer versions, I got burned on importing EMail Activities, which I'll go over later.

Document Version 1.5, Modified 11 March 2005


Getting Started

  1. Install Microsoft CRM

    You won't get far without this first step. After installing, familiarize yourself with the interface and then modify fields, add to the database schema, add or modify drop-down list choices, etc. You must do this if you have any additional GoldMine fields or custom drop-down choices that aren't available by default. Modifying the database schema is beyond the scope of this document, please refer to the Implementation Guide included with MSCRM for more information about CRM customization.

    You should also create at least one user. If you plan on migrating record ownership from GoldMine to CRM you should create all the users that will own records. Since I don't use that feature, I just created a Public Owner user to whom I assign all records.

  2. Install CRM Data Migration Framework

    This comes on its own CD along with the CRM disks. It installs some help files as well as a few command line programs and SQL queries, and also creates the cdf_mscrm database that will be used to transfer your data from GoldMine to MS CRM.

    The files you should pay the most attention to in the Microsoft CRM Data Migration Framework directory are:

    CDF\Microsoft.Crm.DataMigration.InitializeCDF.exe
    This command line program preps the cdf_mscrm database with your CRM customizations so your data can be migrated properly.
    CDFMigrationTool\Microsoft.Crm.DataMigrationTool.exe
    This command line program is the one that you call to perform your final data import into MS CRM.
    Documentation\DataMigrationFrameworkGuide.pdf
    This file explains most of the data import process.
    Documentation\Planning Tools\GoldMineToCDFMapping.xls
    Believe it or not, Microsoft has already done most of your data mapping for you. This Excel file is extremely helpful and should familiarize you with the cdf_mscrm database schema design as well as how data from GoldMine fits into it.

  3. Backups are crucial

    Before you do anything, back up all of your MS CRM database (there should be three), as well as the cdf_mscrm database. If you don't do this, you may have to reinstall both the Data Migration Framework and CRM to get back to where you started if you run in to trouble.

  4. Prepare the cdf_mscrm database

    Remember the customization step from above? Now we'll publish the changes we made to the intermediate cdf_mscrm database so we can start importing our data. After you've made your customization changes you need to run Program Files\Microsoft CRM Data Migration Framework\CDF\Microsoft.Crm.DataMigration.InitializeCDF.exe, which will automatically migrate your CRM customizations into the cdf_mscrm database.

  5. Backup revisited

    Back up your cdf_mscrm database again, now that you've integrated your CRM customizations.

  6. Table Tweaking

    I modified cdf_mscrm.cdf_Annotation.AnnotationId, cdf_mscrm.cdf_CustomerAddress.CustomerAddressId, and cdf_mscrm.cdf_Activity.ActivityId to be Identity (1,1) because of the way I imported data. You may or may not want to do it this way. It doesn't hurt anything, though. I needed a unique integer for each table's index and that was the easiest way to do it.


Data Preparation

  1. Copy your database tables

    I copied the following tables from the GoldMine database to my new CRM database so I could work on a copy of them on my CRM server. It's not a good idea to work on the only live copy of your data, especially if you're still using it.

      contact1
  contact2
  contsupp
  conthist
  cal

    You may need additional tables, but that is up to you and beyond the scope of this document.

  2. Data Indexing Problem

    Unfortunately, the GoldMineToCDFMapping.xls file file wasn't entirely accurate for our implementation, perhaps due to the fact that we upgraded from an earlier version of GoldMine previously. In any case, if your accountno field is an integer, you're home free. Mine wasn't, and I had to create an index table to handle the conversion:

    CREATE TABLE goldminebackup.[dbo].cdfindex(
	rownumber INT identity(1,1),
	accountno VARCHAR(20),
	recid VARCHAR(15),
	contact varchar(40),
	fname nvarchar(50),
	lname nvarchar(50)
)
    rownumber
    The new index for the cdf_mscrm tables
    accountno
    The GoldMine account number
    recid
    The GoldMine recid for additional contacts that aren't in the contact tables

    I am compelled to say that GoldMine has an incredibly poor table design, where values are often overloaded many times and data are scattered everywhere—if you have used it much you will be very grateful to know that CRM does a much better job of organizing data.

    contact
    The GoldMine Contact1.contact value (the full name)
    fname
    A field I created to hold the first name of the contact after I parse it out

    Once again, GoldMine's poor database design rears its ugly head. GoldMine does store last names separately, but if you want just the first name you are completely out of luck. I wrote a program that parses the lastname field and subtracts it from the contact field, then inserts that portion into the fname field. Not a perfect solution, but it works. You'll probably want to do something similar.

    lname
    The GoldMine Contact1.lastname value

    Next I need to populate the table with values, so I run:

    INSERT goldminebackup.[dbo].cdfindex(accountno)
SELECT DISTINCT accountno FROM goldminebackup.[dbo].contact1

    and

    INSERT goldminebackup.[dbo].cdfindex(recid)
SELECT recid FROM goldminebackup.[dbo].contsupp 
WHERE rectype='C' AND contsupref IS NULL
AND (contact IS NOT NULL AND contact NOT LIKE '')

    the first query inserts the main contacts, and the second the additional contacts from GoldMine's contsupp table.

  3. Accounts and Contacts

    CRM uses accounts (companies) and contacts in a much more intelligent way than GoldMine. Our requirements were that every contact has a parent account, even if it's just one with the contact's name. The rest of this document assumes you are in the same boat, and if not you will need to modify a few of the following steps to avoid creating accounts for every contact.

    This query is pretty straight forward:

    UPDATE contact1 SET company=contact
WHERE company IS NULL or company LIKE ''

  4. Fix the names

    Now is when you should fix the first name problem. I wrote an ODBC program in C++ to parse names, delete the lastname from the contact value, and insert the remaining parts as the cdfindex.fname. This program would do you no good because it connects specifically to my database, so you need to do your own thing here. Thinking back on it, I should have written it in PHP, it would have been easier and would have ran much faster.

    Alwin, a visitor to this page, recommended the following procedure for fixing the names that doesn't require any programming. I can't vouch for this, as I haven't tested it, but I have provided it in case it may be useful to other people in our situation:

    -- separate first and last name for contact1 table
update ind
            set        fname = left(co.contact,CHARINDEX(co.lastname,co.contact)-1),
                        lname = co.lastname
            from contact1 co
            inner join cdfindex ind  on ind.accountno = co.accountno

-- separate first and last name for contsupp table          
update ind
            set
                        fname = left(co.contact, CHARINDEX(' ',co.contact)-1),
                        lname = right(co.contact, len(co.contact)-CHARINDEX(' ',co.contact))
            from contsupp co
            inner join cdfindex ind  on ind.recid = co.recid
            WHERE (co.rectype='C') and (CHARINDEX(' ',co.contact)> 0)


Data Transformation

I created several Data Transformation Packages to aid in migrating data. Each has its own part to do, you may need to modify them or create new ones to handle your specific data.

  1. GoldMine Company to CRM Account

    We need an Account for each Contact in CRM, so I created a DTS package with the following query:

    SELECT i.rownumber, c.COMPANY, c.PHONE1, c.PHONE2, c.FAX, c.ADDRESS1,
c.ADDRESS2, c.ADDRESS3, c.CITY, c.STATE, c.ZIP, c.COUNTRY, '18' as OwningUser,
c.key1 AS 'RelationshipType', c.key2 AS 'IndustryCodeName'
FROM contact1 c 
JOIN cdfindex i ON c.accountno = i.accountno
WHERE c.COMPANY NOT LIKE '' OR c.COMPANY IS NOT NULL
ORDER BY rownumber

    The destination being the cdf_mscrm.cdf_Account table. Note the '18' as OwningUser, this was pulled from the cdfindex.rownumber value to set every imported record in CRM to be owned by the same user. If you want to preserve ownership for different records, you're on your own here.

    Since my GoldMine backup tables are on the same SQL server as my CRM tables, I just created two connections to the local SQL server to transfer data between the different databases.

    For the Transformations, I mapped as follows (Source->Destination):

      rownumber->AccountId
  rownumber->PrimaryContactId (I can do this because my Accounts and Contacts share the same ID)
  COMPANY->Name
  PHONE1->Telephone1
  PHONE2->Telephone2
  FAX->Fax
  ADDRESS1->Address1_Line1
  ADDRESS2->Address1_Line2
  ADDRESS3->Address1_Line3
  CITY->Address1_City
  STATE->Address1_StateOrProvince
  ZIP->Address1_PostalCode
  COUNTRY->Address1_Country (don't confuse this with Address1_County)
  OwningUser->OwningUser
  RelationshipType->CustomerTypeCodeName
  IndustryCodeName->IndustryCodeName

    Uncheck Use fast load!

    It is extremely important that you uncheck use fast load on the Options tab; your data will not be transformed properly if you don't! This is true for all DTS packages we will create!

  2. GoldMine Contacts to CRM Contacts

    Now to migrate the main part, the contacts:

    SELECT DISTINCT i.rownumber, i.fname, c.CONTACT, c.LASTNAME, c.TITLE, c.PHONE1,
	c.PHONE2, c.PHONE3, c.FAX, c.ADDRESS1, c.ADDRESS2,
	c.ADDRESS3, c.CITY, c.STATE, c.ZIP, c.COUNTRY,
	c.DEAR, '18' AS 'OwningUser'
FROM cdfindex i 
JOIN contact1 c ON i.accountno = c.accountno
WHERE i.recid IS NULL
ORDER BY RowNumber

    The destination here is cdf_mscrm.cdf_Contact, and the mappings are similar to the Account migration:

      rownumber->ContactId
  rownumber->AccountId
  fname->FirstName
  CONTACT->NickName (I did this to preserve the full contact string somewhere in CRM)
  LASTNAME->LastName
  TITLE->JobTitle
  PHONE1->Telephone1
  PHONE2->Telephone2
  PHONE3->MobilePhone
  FAX->Fax
  ADDRESS1->Address1_Line1
  ADDRESS2->Address1_Line2
  ADDRESS3->Address1_Line3
  CITY->Address1_City
  STATE->Address1_StateOrProvince
  ZIP->Address1_PostalCode
  COUNTRY->Address1_Country
  DEAR->Salutation
  OwningUser->OwningUser

    Once again, make sure Use fast load is OFF. We used the PHONE3 field in GoldMine for cell phones, so the above mapping worked for us. You may need to adapt this to your data.

  3. Secondary Contact Migration

    Since GoldMine stores additional contacts outside the Contact1 and Contact2 tables, you'll want a package that does this:

    SELECT i.rownumber, s.*, '18' AS 'OwningUser', x.rownumber AS 'ParentAccount'
FROM cdfindex i
JOIN contsupp s ON i.recid = s.recid
JOIN cdfindex x ON s.accountno = x.accountno
WHERE i.accountno IS NULL
AND s.rectype='C'

    The destination is also the cdf_Contact table, since CRM stores all contacts as contacts, unlike GoldMine.

    The mappings are as follows:

      rownumber->ContactId
  CONTACT->FirstName
  TITLE->JobTitle
  DEAR->Salutation
  PHONE->Telephone1
  FAX->Fax
  ADDRESS1->Address1_Line1
  ADDRESS2->Address1_Line2
  ADDRESS3->Address1_Line3
  CITY->Address1_City
  STATE->Address1_StateOrProvince
  ZIP->Address1_PostalCode
  COUNTRY->Address1_Country
  OwningUser->OwningUser
  ParentAccount->AccountId

    Once more, turn off Use fast load.

Data Transformation, Part Two

  1. Product Import

    I created a small table containing basic product information to import, just to save the trouble of creating them in CRM. The table contains:

    CREATE TABLE products(
	ProductId int identity(1,1) NOT NULL PRIMARY KEY,
	Name nvarchar(250) NOT NULL,
	Description nvarchar(250) NOT NULL,
	Price money NOT NULL,
	ProductSku nvarchar(50) NOT NULL
)

    You don't have to do it this way, but it was easier for me, so I created another DTS package for products by copying the product table I created with the following mappings (to the cdf_mscrm.cdf_Product table):

      Product->ProductId
  Name->Name
  Description->Description
  Price->Price
  ProductSku->ProductNumber

  2. Additional Addresses

    Some accounts have multiple addresses for billing, shipping, etc. (It really helps to clean up the contsupref field for each of these addresses to specify the address type). I created a package to migrate them:

    SELECT i.rownumber, s.*, '2' AS 'ObjectTypeCode' FROM contsupp s
JOIN cdfindex i ON s.accountno = i.accountno
WHERE s.rectype='C'
AND s.contsupref LIKE '%address%'
ORDER BY i.rownumber

    The destination this time is the cdf_mscrm.cdf_CustomerAddress and the transformations are as follows:

      rownumber->ParentId
  CONTACT->PrimaryContactName
  CONTSUPREF->AddressTypeCodeName *
  PHONE->Telephone1
  FAX->Fax
  ADDRESS1->Address1_Line1
  ADDRESS2->Address1_Line2
  ADDRESS3->Address1_Line3
  CITY->Address1_City
  STATE->Address1_StateOrProvince
  ZIP->Address1_PostalCode
  COUNTRY->Address1_Country
  ObjectTypeCode->ObjectTypeCode

    * This has to be cleaned up with a SQL query later, since the AddressTypeCodeName isn't migrated. I strongly recommend normalizing your AddressTypeCodeName data (SELECT DISTINCT values for this field and clean them up) before you run this DTS package, it'll make things much easier later.

  3. Notes

    Notes in Goldmine are knows as Annotations in CRM, so you want to migrate them to the cdf_mscrm.cdf_Annotation table:

    SELECT i.rownumber, 'Notes' AS Subject, c.Notes AS NoteText,
	'18' as OwningUser, '0' AS IsDocument,
	'1' AS ObjectTypeCode
FROM contact1 c
JOIN cdfindex i ON c.accountno = i.accountno
WHERE c.notes IS NOT NULL
AND c.Notes NOT LIKE ''

    And transform the data as follows:

      rownumber->ObjectId
  Subject->Subject
  NoteText->NoteText
  OwningUser->OwningUser
  IsDocument->IsDocument
  ObjectTypeCode->ObjectTypeCode

Data Transformation of Notes

  1. Cal Notes

    The GoldMine.cal table contains several different types of information (another gripe from me about the way they store data). I found that entries in Cal with a rectype of either T (Next Action), O (Other), S (Sales Potential), or M (Message) belong in Annotations in CRM, so I created a DTS package for them with the following SQL query:

    SELECT i.rownumber, a.ref AS Subject, a.notes AS NoteText, a.createon,
	'18' AS OwningUser,'0' AS IsDocument,'1' AS ObjectTypeCode
FROM goldminebackup.[dbo].cal a
JOIN goldminebackup.[dbo].cdfindex i ON a.accountno = i.accountno
WHERE a.rectype LIKE '[TOSM]'
AND a.ref IS NOT NULL

    The destination is cdf_mscrm.cdf_Annotation, and the mappings are as follows:

       rownumber->ObjectId
  Subject->Subject
  NoteText->NoteText
  createon->DocumentBody*
  OwningUser->OwningUser
  IsDocument->IsDocument
  ObjectTypeCode->ObjectTypeCode

    * The CreateOn field, is not supported in DTS for annotations, so I copy it to this location and run a query to fix it later.

  2. Conthist Notes

    The GoldMine.Conthist table also has several bits that belong in Annotations in CRM. Through poking around in the table, I found that those Conthist records with a rectype of E (Event), L (Form), T (Next Action), S (Sale) would map straight over. I also discovered that some of the records with a rectype of O (Other) would also be Annotations, but only if they contained a resultcode of SEM, ACK, DON, or SNT, so I created a DTS package with the following query:

    SELECT i.rownumber, h.ref AS Subject, h.notes AS NoteText, h.createon,
	'18' AS OwningUser,'0' AS IsDocument,'2' AS ObjectTypeCode
FROM goldminebackup.[dbo].conthist h
JOIN goldminebackup.[dbo].cdfindex i ON h.accountno = i.accountno
WHERE h.rectype LIKE '[ELTS]'
OR (h.rectype = 'O' AND (
	h.resultcode = 'SEM'
	OR h.resultcode = 'ACK'
	OR h.resultcode = 'DON'
	OR h.resultcode = 'SNT')
)
AND h.ref IS NOT NULL

    The mappings are exactly the same as in the previous DTS package.

Data Transformation of Activities

  1. Activities from Cal

    Now for the fun part. Both Cal and Conthist contain fields that belong in the cdf_mscrm.cdf_Activity table, but in different categories, so I wrote DTS packages for each type, starting with Cal's phone calls:

    SELECT '0' AS ActTypCode,'Phone Call' AS ActivityTypeCodeName,
	a.ref, a.notes,'18' AS OwningUser,i.rownumber, a.createon,
	'1' AS ObjectTypeCode,'1' AS StateCode,'0' AS DirectionCode
FROM goldminebackup.[dbo].cal a
JOIN goldminebackup.[dbo].cdfindex i ON a.accountno = i.accountno
WHERE a.rectype='C'
AND a.ref IS NOT NULL

    The destination is cdf_mscrm.cdf_Activity, and maps as follows:

      ActTypCode->ActivityTypeCode
  ActivityTypeCodeName->ActivityTypeCodeName
  ref->Subject
  notes->Description
  OwningUser->OwningUser
  rownumber->ObjectId
  createon->Misc1*
  ObjectTypeCode->ObjectTypeCode
  StateCode->StateCode
  DirectionCode->DirectionCode

    Unfortunately, you can't insert a NULL value into ActivityTypeCode, so I put 0 there, and fill out the ActivityTypeCodeName with one of the following default values: Task, Fax, Phone Call, E-Mail, Letter, Appointment, or Resolution Activity. Later we'll use the provided stored procedures to help populate the corresponding codes.

    Since we're importing these records as they relate to a Contact, you'll want to be sure the ObjectTypeCode is 2, which represents a Contact, as opposed to an Account (ObjectTypeCode 1)—see the GoldMineToCDFMapping Excel file that comes with the Data Migration Framework for more details.

    * The CreateOn field also doesn't translate directly to the Activity table, so we store it and fix it later.

  2. E-Mail Activities from Cal

    Now for the E-Mail correspondence. Create a DTS package with this query:

    SELECT '0' AS ActTypCode, 'E-mail' AS ActivityTypeCodeName,
	a.ref, a.notes,'18' AS OwningUser,i.rownumber, a.createon,
	'1' AS ObjectTypeCode,'1' AS StateCode
FROM goldminebackup.[dbo].cal a
JOIN goldminebackup.[dbo].cdfindex i ON a.accountno = i.accountno
WHERE a.rectype='Q'
AND a.ref IS NOT NULL

    and map it just like the one above except there is no DirectionCode.

    Cal records with a rectype of Q refer to E-Mail correspondence, so we map them to the E-Mail Activity in CRM.

  3. E-Mail Activities from Conthist

    Conthist also contains E-Mail activities, when the rectype is M (sent message), MG (E-Mail message), MO (sent E-Mail), or MI (received E-Mail). Create a DTS package for them with the following query:

    SELECT '0' AS ActTypCode,'E-Mail' AS ActivityTypeCodeName,
	c.ref, c.notes,'18' AS OwningUser,i.rownumber, c.createon,
	'1' AS ObjectTypeCode,'1' AS StateCode
FROM goldminebackup.[dbo].conthist c
JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
WHERE c.rectype='M' OR c.rectype='MG'  OR c.rectype='MO'
OR c.rectype='MI'
AND c.ref IS NOT NULL

    and map the data as in the previous step.

  4. Fax Activities

    Conthis records with a rectype of O and a resultcode of FAX map in to Activities as well with the query:

    SELECT '0' AS ActTypCode,'Fax' AS ActivityTypeCodeName,
	c.ref, c.notes,'18' AS OwningUser,i.rownumber, c.createon,
	'1' AS ObjectTypeCode,'1' AS StateCode,
	'Outgoing' AS DirectionCodeName
FROM goldminebackup.[dbo].conthist c
JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
WHERE c.rectype='O' AND c.resultcode='FAX'
AND c.ref IS NOT NULL

    and map the data as before.

  5. Incoming Phone Activities

    Conthist records with a rectype of CI are incoming phone call activities, find them with this query:

    SELECT '0' AS ActTypCode,'Phone Call' AS ActivityTypeCodeName,
	c.ref, c.notes,'18' AS OwningUser,i.rownumber, c.createon,
	'1' AS ObjectTypeCode,'1' AS StateCode,
	'Incoming' AS DirectionCodeName
FROM goldminebackup.[dbo].conthist c
JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
WHERE c.rectype='CI'
AND c.ref IS NOT NULL

    and map them like the other activities.

  6. Outgoing Phone Activities

    Conthist records with rectypes of CC (call back), CM (returned message), or CO (outgoing call) are also phone activities, create a DTS package with this query:

    SELECT '0' AS ActTypCode,'Phone Call' AS ActivityTypeCodeName,
	c.ref, c.notes,'18' AS OwningUser,i.rownumber, c.createon,
	'1' AS ObjectTypeCode,'1' AS StateCode,
	'Outgoing' AS DirectionCodeName
FROM goldminebackup.[dbo].conthist c
JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
WHERE c.rectype='CC' OR c.rectype='CM' OR c.rectype='CO'
AND c.ref IS NOT NULL

    and map as before.

More Details

  1. E-Mail Addresses

    You undoubtedly need to migrate contact E-Mail addresses. The way GoldMine stores E-Mail addresses is absolutely retarded, so you'll need to run queries like these:

    UPDATE cdf_Contact SET EmailAddress1=s.contsupref
FROM goldminebackup.[dbo].contsupp s
JOIN goldminebackup.[dbo].cdfindex i ON s.accountno = i.accountno
WHERE s.contact='E-mail Address'
AND contactId = i.rownumber

    and

    UPDATE cdf_Contact SET EmailAddress1 = s.contsupref
FROM goldminebackup.[dbo].contsupp s
JOIN goldminebackup.[dbo].cdfindex i ON s.linkacct = i.recid
WHERE s.contact = 'E-mail Address'
AND contactId = i.rownumber

    Note that if you have mutliple E-Mail addresses for a single contact, this query does not address them. You're on your own here.

  2. Contact Source

    Since my company decided against using the Lead section of CRM, we needed to record where a Contact came from in the Contact, so we added a Source field to the database schema for the AccountBase table. This field comes back in to cdf_mscrm as cdf_Account_ext.CFSSource, so I wrote a query to copy that as well:

    UPDATE cdf_Account_ext SET CFSSource=c.source
FROM goldminebackup.[dbo].contact1 c
JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
WHERE cdf_Account_ext.AccountId = i.rownumber
AND c.accountno IS NOT NULL
AND c.source IS NOT NULL

  3. Websites

    If you have some website URI's for your contacts, you'll want to run this query:

    UPDATE cdf_Contact SET WebSiteUrl = s.contsupref
FROM goldminebackup.[dbo].contsupp s
JOIN goldminebackup.[dbo].cdfindex i ON s.accountno = i.accountno
WHERE s.rectype = 'P'
AND s.contact = 'Web Site'
AND ContactId = i.rownumber

  4. Migrate Do Not Contact information

    GoldMine doesn't do anything about this, but CRM has separate fields for each type of contact. If you store Do Not Contact information like we did, you'll need queries to migrate it to the intermediate database similar to ours:

    UPDATE cdf_Contact SET DoNotBulkEmail = 0

UPDATE cdf_Contact SET DoNotBulkEmail = 1 WHERE ContactId IN
(
	SELECT i.rownumber FROM goldminebackup.[dbo].contact1 c
	JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
	WHERE c.u_key4 LIKE '%email%' OR c.u_key4 LIKE '%e-mail%'
)

UPDATE cdf_Contact SET DoNotPostalMail = 1 WHERE ContactId IN
(
	SELECT i.rownumber FROM goldminebackup.[dbo].contact1 c
	JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
	WHERE c.u_key4 LIKE '% mail%'
)

UPDATE cdf_Contact SET DoNotFax = 1 WHERE ContactId IN
(
	SELECT i.rownumber FROM goldminebackup.[dbo].contact1 c
	JOIN goldminebackup.[dbo].cdfindex i ON c.accountno = i.accountno
	WHERE c.u_key4 LIKE '%fax%'
)

  5. Fix Secondary Addresses

    Remember this from earlier? We have to fix the type of secondary addresses we imported with the following queries:

    UPDATE cdf_CustomerAddress set AddressTypeCode=1
WHERE AddressTypeCodeName = 'Billing Address'

UPDATE cdf_CustomerAddress set AddressTypeCode=2
WHERE AddressTypeCodeName = 'Shipping Address'

UPDATE cdf_CustomerAddress set AddressTypeCode=4
WHERE AddressTypeCodeName = 'Additional Address'
OR AddressTypeCodeName = 'Alternate Address'
OR AddressTypeCodeName = 'Business Address'
OR AddressTypeCodeName = 'Physical Address'
OR AddressTypeCodeName = 'Mailing Address'
OR AddressTypeCodeName = 'Home Address'

    Of course, this assumes you have your address types mapped out nicely like I did in GoldMine before you run the Additional Address DTS Package we created earlier.

Drop-down Lists

  1. Drop-down List Cleanup

    CRM has excellent stored procedures to help you migrate drop-down list information. You must have already populated any drop-down lists in CRM and ran the CDF Initialization tool for this step to work.

  2. Populate Your String Maps

    Run p_cdf_PopluateStringMap in Query Analyzer to prepare a mapping of all your current drop-down fields.

  3. Map Matching Values

    Run p_cdf_AutoMapPickLists to automagically map values that are an exact match. If you cleaned up your data before you got here, this is where it will really pay off.

  4. Fix Unmapped Values

    Run p_cdf_ListUnmappedPickListValues 'entity', where entity is the name of the entity mapping you are trying to fix, such as 'Account'.

    Run queries to fix the data, then go back to the first step and start over. When you get no more unmapped results, continue to the next step.

  5. Populate Entity Tables With Values

    Run p_cdf_PopulateEntityPicklists '0' to populate entity tables with updated drop-down list values, ignoring unmapped ones.

  6. Repeat As Needed

    You should repeat this process for all other entities that you have customized or are migrating as needed.

Begin the Import

  1. Start Importing Data

    Now that wasn't so hard, was it? (It only took me five weeks to get this process down, but then I didn't have this document for reference, did I?)

    Run the following program: CRM Data Migration Framework\CDFMigrationTool\Microsoft.Crm.DataMigrationTool.exe. If everything is okay, you'll see it start up and then print dots as each record is imported in to the CRM database. Sit back and relax, for my import of roughly 85k contact and account records, with roughly 160k activities and 130k annotations, this part takes about 19 hours on a 2.8Ghz P4 with 1Gb RAM and mirrored SATA drives.

    I never found a way to see the progress of the import, other than to time the actual process and use that time as a benchmark for the next import test. It helps to turn off every unneeded service, but that is up to you.

  2. Check Data, repeat as necessary

    Verify that your data comes in properly. If not, analyze why, fix it, then begin this process again.

After the Import

One of the nicest things about CRM is how easy it is to get more information into the system, even after you've run your big import. The cdf_mscrm database stores the Id of the destination record in CRM, and knows what it has and hasn't imported. If you find, after your import, you missed some data but can't go back and do another import, rest assured that all you have to do is write and run queries or data transformation packages to copy the data into cdf_mscrm and then re-run your DataMigrationTool.exe. It will import the missing information and link it correctly as long as you have in your CDF database.

Thanks Are Appreciated

Over the past few months this document has been on the Internet, I've received several E-Mails both thanking me and asking some new questions. I'm happy to answer any questions about this process I can, but please realize that my day job is quite busy, and I might not always get a very timely response to you. I love to hear from people that find this document useful, so if you do, please send me an E-Mail. Of course, if you're feeling generous you are welcome to pick me up something from my Amazon wishlist.

14 Comments »

14 Responses to “Migrating GoldMine to Microsoft CRM”

  1. Olaf says:
    2006 August 1 - Tuesday at 11:04 am

    Hi there,

    we are just about to change form goldmine to MS CRM. Your tool might be of interest.
    Please send more information.

    Thanks

    Olaf

  2. Larry's Taco Talk : GoldMine to CRM (part 1) says:
    2007 February 7 - Wednesday at 6:48 pm

    [...] Microsoft has included a tool for migrating from various other systems to CRM, including GoldMine. I figured, as an MVP, I at least ought to try to learn this. I can always move to Scribe if need be. The tool is Data Migration Framework and can be downloaded from here. The tool comes with some spreadsheets for a few systems including GoldMine. These spreadsheets outline where the data is in these systems. This is nice. the DMF walks you through a process to migrate data. However, the most important step, populating the intermediate database, cdf_mscrm, is pretty much left up to the user. To populate this, one uses SQL Data Transformation Services, DTS. To do so, one must be well versed in DTS. The DMF gives no guidance here. I Googled for info on DMF and GoldMine and found little to no useful information. Even the CRM 'back channel' did not respond to my requests for assistance and guidance. I did find ONE posting however that was my salvation. This was a procedure written for migrating GoldMine to CRM 1.2 back in 2005. It is really quite a good tutorial and procedure for the process. He includes very detailed instructions and rationale for each step in the process. Of course I did have to make some adjustments for 3.0 and to account for doing it on my systems as opposed to his data. This wonderful post has the unlikely title of Scurvy Jake's Pirate Blog (rated arrrrrrrrrrrr)! The posting I used is http://bogomip.net/blog/migrating-goldmine-to-microsoft-crm/. [...]

  3. Larry Lentz (CRM MVP) says:
    2007 February 7 - Wednesday at 6:52 pm

    Thanks for this post. This is the ONLY definitive guide I've been able to find on how to do this, and my resources as an MVP are considerable.
    I've posted your blog on my blog. It's at http://ts2community.com/blogs/larrylentz/default.aspx

    Thanks again!

  4. Larry's Taco Talk : GoldMine to CRM (part 2) says:
    2007 February 8 - Thursday at 9:01 pm

    [...] of the things that hung me up was the OwningUser field. When doing the DTS transforms, Scurvy Jake's blog inserted '18' as the OwningUser. I blindly used this in my initial trials on my VPC. Nothing [...]

  5. Jonathan says:
    2007 May 17 - Thursday at 1:41 am

    Thank you for posting this information. I have just migrated a Goldmine database to Microsoft CRM 3.0. Your post helped me enormously and saved me a lot of time. I had to change a few bits to make it work for version 3.0 though.

    If anyone is interested, I am posting on how to migrate Goldmine to Microsoft CRM 3.0 on the following page: http://www.sqltree.net/GoldmineToMSCRM.shtml
    I have posted a backlink to your site there.

    Hope this helps anybody else with the same problem/task. Your post definitely helped me.

  6. Migrating to MS CRM CDF format at Rage on Omnipotent says:
    2008 January 8 - Tuesday at 9:37 am

    [...] though we are not using Goldmine, I found this detailed summary of a CRM migration process very [...]

  7. John Zaharakis says:
    2008 January 14 - Monday at 12:10 pm

    Thank you so much for this post. It is the only one I was able to find in reference to migrating Goldmine to CRM. With your post I was able to complete a Goldmine 6.7 to CRM 3.0 migration, obviously with a couple of changes but this post was the foundation of my migration. Thank you again and thanks for saving my company $1500 for purchasing Scribe for 60 days. I am in the works of trying to get you a gift from my company. I will try my best.

    John

  8. Webmonster says:
    2008 January 14 - Monday at 9:14 pm

    Hey thanks, I really appreciate it. There's a link here on my blog to my Amazon wishlist. I'm glad it is still helping people out.

  9. John Zaharakis says:
    2008 January 22 - Tuesday at 11:17 am

    Hello, I am almost done with my migration, just one question.. How did you move the body of the email over? Thanks in advance for your help.

  10. Tom Roth says:
    2008 November 12 - Wednesday at 10:05 pm

    very informative post thank you

  11. Goldmine to CRM | keyongtech says:
    2009 January 21 - Wednesday at 7:51 pm

    [...] Best-practises documentation. > >Hope someone can help. See this for a step by step guide http://bogomip.net/blog/index.php/mi...microsoft-crm/ Julian Sharp Vigence for MS CRM in the UK See my MSCRM blog [...]

  12. Doug Steinschneider says:
    2010 January 25 - Monday at 1:45 pm

    I now deploy Dynamics CRM but in the past was deeply involved with GoldMine and still have about 600 users I support. I post sql queries I create to http://www.crmsql.com. Those queries might be worth a look for those trying to sort out GoldMine email adresses etc. I will put a link to this blog on the site

  13. Scurvy Jake says:
    2010 January 25 - Monday at 2:51 pm

    Thanks for the information. Mine is a bit dated now, but people still find it useful from time to time. I'm sure they'll appreciate your queries.

  14. Lubos Andrlik says:
    2010 March 15 - Monday at 2:14 am

    Hi guys,

    I'm trying to pull the data from GM, but I can't get the primary email of the primal contact. I'm OK with the secondary contact, resp. the structure of CONTSUPP table, but I still cannot find an email address of the contacts from CONTACT1 table.
    .
    .
    .
    I just came to it. The primary contact email is stored CONTSUPP (with empty or null reference in LINKACCT attribute). Just wanted to share this with others. I spend a couple of hours with this.

    Cheers, Lubos