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
- 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.
- 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.
- 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. - Prepare the
cdf_mscrm
databaseRemember 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 runProgram Files\Microsoft CRM Data Migration Framework\CDF\Microsoft.Crm.DataMigration.InitializeCDF.exe
, which will automatically migrate your CRM customizations into the cdf_mscrm database. - Backup revisited
Back up your
cdf_mscrm
database again, now that you've integrated your CRM customizations. - Table Tweaking
I modified
cdf_mscrm.cdf_Annotation.AnnotationId
,cdf_mscrm.cdf_CustomerAddress.CustomerAddressId
, andcdf_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
- 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.
- 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 - 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
- lname
- The GoldMine
Contact1.lastname
value
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.
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 thefname
field. Not a perfect solution, but it works. You'll probably want to do something similar.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. - 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 ''
- 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 thecontact
value, and insert the remaining parts as thecdfindex.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.
- 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' asOwningUser
, this was pulled from thecdfindex.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! - 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. - Secondary Contact Migration
Since GoldMine stores additional contacts outside the
Contact1
andContact2
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
- 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
- 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 yourAddressTypeCodeName
data (SELECT DISTINCT values for this field and clean them up) before you run this DTS package, it'll make things much easier later. - 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
- 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. - 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
- Activities from
Cal
Now for the fun part. Both
Cal
andConthist
contain fields that belong in thecdf_mscrm.cdf_Activity
table, but in different categories, so I wrote DTS packages for each type, starting withCal
'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 theActivityTypeCodeName
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 theGoldMineToCDFMapping
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. - 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 arectype
of Q refer to E-Mail correspondence, so we map them to the E-Mail Activity in CRM. - E-Mail Activities from
Conthist
Conthist
also contains E-Mail activities, when therectype
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.
- Fax Activities
Conthis
records with arectype
of O and aresultcode
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.
- Incoming Phone Activities
Conthist
records with arectype
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.
- Outgoing Phone Activities
Conthist
records withrectype
s 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
- 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.
- 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 theAccountBase
table. This field comes back in tocdf_mscrm
ascdf_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
- 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
- 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%' )
- 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
- 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.
- Populate Your String Maps
Run
p_cdf_PopluateStringMap
in Query Analyzer to prepare a mapping of all your current drop-down fields. - 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. - Fix Unmapped Values
Run
p_cdf_ListUnmappedPickListValues 'entity'
, whereentity
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.
- Populate Entity Tables With Values
Run
p_cdf_PopulateEntityPicklists '0'
to populate entity tables with updated drop-down list values, ignoring unmapped ones. - Repeat As Needed
You should repeat this process for all other entities that you have customized or are migrating as needed.
Begin the Import
- 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.
- 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.
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
[...] 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/. [...]
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!
[...] 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 [...]
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.
[...] though we are not using Goldmine, I found this detailed summary of a CRM migration process very [...]
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
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.
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.
very informative post thank you
[...] 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 [...]
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
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.
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