I spent hours discovering what would be the best tool for the migration. I tried Scribe, Starfish and several other products listed in wiki but any of tools satisfied my needs in data extraction from GoldMine for 100%. So I decided to use SSIS Integration Toolkit.
1. First that you need is to write correct query that will give you appropriate data. Here is the query I used in my migration (in case you will have additional fields to be migrated you can extend this script):
Select c1.COMPANY ,case when LEN(contact) - LEN(lastname)-1 <= 0 then '' else Substring(CONTACT, 1, LEN(contact) - LEN(lastname)-1) end firstname ,LASTNAME ,IsNull(c1.PHONE1, '') + ISNULL(' ext ' + c1.EXT1, '') Phone1 ,IsNull(c1.PHONE2, '') + ISNULL(' ext ' + c1.EXT2, '') Phone2 ,IsNull(c1.PHONE3, '') + ISNULL(' ext ' + c1.EXT3, '') Phone3 ,c1.FAX ,c1.ADDRESS1 ,c1.ADDRESS2 ,c1.ADDRESS3 ,c1.CITY ,c1.STATE ,c1.ZIP ,c1.COUNTRY ,(select top 1 contsupref from CONTSUPP c where c.RECTYPE = 'P' and c.CONTACT = 'web site' and c.ACCOUNTNO = c1.ACCOUNTNO) websiteurl ,(select top 1 CONTSUPREF from CONTSUPP cu where cu.CONTACT = 'E-mail Address' and cu.ACCOUNTNO = c1.ACCOUNTNO) emailaddress ,1 customeridtype From CONTACT1 c1 where c1.COMPANY != ''
2. Once you finished with query create new SSIS project using BIDS (to make migration to CRM 2011 possible you will have to have SSIS Integration Toolkit already installed on your server):
Drag-n-Drop DataFlow task:
Open DataFlow task with doubleclick and add ADO.Net to your DataFlow:
Configure connection to your Goldmine DB:
Configure selection of information from Goldmine DB (using select statement that I provided in step 1 - you can recheck that your statement is valid and data is correct using Preview button):
3. Add CRM Destination to your DataFlow and configure connection to your CRM Server:
Select ‘Create’ in Action dropdown and ‘account’ entity as a ‘Destination Entity’:
Connect ADO.Net Source and CRM Destination components:
Open CRM Destination component and expand Columns tab, map source and destination fields, click Ok:
Add second CRM Destination Component to DataFlow and connect it with first:
For second CRM Destination Component choose the same ‘CRM Connection Manager’ and ‘contact’ as a ‘Destination Entity’:
Open Columns Tab and map fields (please notice that I used identifier of account that was created in previous insert – Id field):
Save you project and start it. I hope you will get your accounts and contacts migrated: