Monday, February 18, 2013

Migrating contacts and accounts from Goldmine to Microsoft CRM 2011

I just finished a project where I had to migrate data from Goldmine to Microsoft CRM 2011 so I decided to share with my results.
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):

Click OK.

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:
To make import of contacts possible in the same stream open CRM Destination component and choose ‘Error Handling’ tab, choose ‘Redirect rows to error output’:

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: