Salesforce to Dynamics CRM Activity Migration

Congratulations! You’ve made the decision to switch from Salesforce.com to Microsoft Dynamics CRM! We may be biased, but we’re pretty positive that you are going to love the features and benefits of Dynamics CRM! Now that you’ve made the switch, you may be wondering how you can migrate your previous Salesforce.com tasks into your new CRM activities. Well, in today’s blog we have those answers for you! So without further ado, let’s begin!

For the created look up tables, we used Scribe Insight to populate the values. You will need to get all of your CRM records and insert SF ID and CRM Guid in the new staging mapping table as well. IDs in Salesforce are made up of an 18-character-long, case-sensitive string and the data source table has several key fields that will drive our logic. The first three characters identify which Salesforce entity the records belongs to. As you can see from the image below, all records in the ID column start with “00T”, which translates to task.

102015 1558 Salesforcet1 Salesforce to Dynamics CRM Activity Migration

This table illustrates some other prefixes commonly used by Salesforce.

102015 1558 Salesforcet2 Salesforce to Dynamics CRM Activity Migration

For this example, we load source data into the SQL Server staging data base. This makes the processes much easier to implement by creating lookup/translation tables for the entities that were loaded into the CRM system previously, thereby eliminating the need to make expensive calls to the CRM services in order to populate lookup fields in the new records.

For instance, when the user import is performed, we create a two fields table:

CREATE
TABLE [dbo].[SF2CRMUserMap](

    [SfId] [nchar](18)
NOT
NULL,

    [CrmId] [uniqueidentifier] NULL

CONSTRAINT [PK_SF2CRMUserMap] PRIMARY
KEY
CLUSTERED

(

    [SfId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY
=
OFF, ALLOW_ROW_LOCKS
=
OFF, ALLOW_PAGE_LOCKS
=
OFF) ON [PRIMARY]

)
ON [PRIMARY]

You then do the same for all other entities previously loaded that you need for a successful Activity assignment. Going forward, you simply need to construct a SQL statement to get the source rows and then prepare them for the CRM import.

SELECT


ISNULL(cb.crmId,
‘6b02362e-6f14-e511-80f0-c4346bac9948’) CreatedByGuid,


ISNULL(mb.crmId,
‘6b02362e-6f14-e511-80f0-c4346bac9948’) ModifiedByGuid,


ISNULL(o.crmId,
‘6b02362e-6f14-e511-80f0-c4346bac9948’) OwnerGuid,

a.CrmGuid as AccountGuid,

WhoEntity =

    CASE

    WHEN
LEFT(t.WhoId, 3)
=
‘003’
THEN
‘contact’

    WHEN
LEFT(t.WhoId, 3)
=
’00Q’
THEN
‘lead’

    WHEN a.CrmGuid IS
NOT
NULL
THEN
‘account’

    WHEN t.WhoId =
‘000000000000000AAA’
THEN
‘systemuser’

    ELSE
NULL

    END,

WhoGuid =

    CASE

    WHEN
LEFT(t.WhoId, 3)
=
’00Q’
THEN (SELECT CRMId FROM SF2CRMLeadMap WHERE SFId = t.WhoId)

    WHEN
LEFT(t.WhoId, 3)
=
‘003’
THEN (SELECT CrmGuid FROM SF2CRMContactMap WHERE SFId = t.WhoId)

    WHEN a.CrmGuid IS
NOT
NULL
THEN a.CrmGuid

    WHEN t.WhoId =
‘000000000000000AAA’
THEN (SELECT crmid FROM SF2CRMUserMap WHERE SFId = t.OwnerId)

    ELSE
NULL

    END,

WhatEntity =

    CASE


WHEN a.CrmGuid IS
NOT
NULL
THEN 1

    WHEN
LEFT(t.WhatId, 3)
=
‘006’
THEN 3

    WHEN
LEFT(t.WhatId, 3)
=
‘a0F’
THEN 123

    WHEN
LEFT(t.WhatId, 3)
=
‘500’
THEN 112

    WHEN
LEFT(t.WhatId, 3)
=
’01t’
THEN 1024

    WHEN
LEFT(t.WhatId, 3)
=
‘701’
THEN 4400

    WHEN
LEFT(t.WhatId, 3)
=
‘001’
THEN 1

    ELSE 8

    END,

    WhatGuid =

    CASE

    WHEN a.CrmGuid IS
NOT
NULL
THEN    a.CrmGuid

    WHEN
LEFT(t.WhatId, 3)
=
‘a0B’
THEN (SELECT CRMId FROM SF2CRMDepartmentMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘006’
THEN (SELECT CRMId FROM SF2CRMOpportunityMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘a0F’
THEN (SELECT CRMId FROM SF2CRMCompetitorMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘500’
THEN (SELECT CRMId FROM SF2CRMCaseMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
’01t’
THEN (SELECT CRMId FROM SF2CRMProductMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘701’
THEN (SELECT CRMId FROM SF2CRMCampaignMap WHERE SFId = t.WhatId)

    WHEN
LEFT(t.WhatId, 3)
=
‘001’
THEN (SELECT CrmGuid FROM SF2CRMAccountMap WHERE SFID = t.WhatId)

    ELSE
NULL

    END,

t.*

FROM sf_Task n

LEFT
OUTER
JOIN SF2CRMAccountMap a ON t.AccountId = a.SFID

LEFT
OUTER
JOIN SF2CRMUserMap cb ON n.CreatedById = cb.SFId

LEFT
OUTER
JOIN SF2CRMUserMap mb ON t.LastModifiedById = mb.SFId

LEFT
OUTER
JOIN SF2CRMUserMap o ON t.OwnerId = o.SFId

As you can see, there are some default GUIDs set in the query. This is done in order to assign records from the users that didn’t make it into CRM to a default/migration user.

Now you have the query ready, tuned, and the indexes on the lookup fields set. The next step will be to set the mapping in Scribe Insight and map the fields accordingly.

102015 1558 Salesforcet3 Salesforce to Dynamics CRM Activity Migration

102015 1558 Salesforcet4 Salesforce to Dynamics CRM Activity Migration

State code and status code formulas used for this mapping are below:

S21 – “IsClosed” bit field and S14 – “Status” text field.

IF(TRIM(S21),1,0)

IF(S14=”Completed”,5,IF(S21,5,

(IF(

ISPICKLISTVALUE(TRIM(S14),”CRM”,”task”,”statuscode”,””),

TRIM(S14),2))))

All that’s left now is to map the other fields accordingly, test, and run. When the job is finished, you will have all the tasks set and assigned to the appropriate records in CRM!

That’s all for our blog today! Still not sure if making the switch from Salesforce.com to Microsoft Dynamics CRM is right for you? Let us convince you!

Happy CRM’ing!

 Salesforce to Dynamics CRM Activity Migration

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

PowerObjects- Bringing Focus to Dynamics CRM