Microsoft CRM – Data Migration Framework (DMF) Bugs / Fixes

I’ve been migrating data for a client recently and part of this has required the use of the MS Data Migration Framework (DMF).  I thought I’d post a couple of bugs I found and(thankfully) how I managed to get past them with the use of SQL Profiler!


The Migration Framework works – albeit slowly as it uses the CRM Web Service to stuff the records into the CRM database.  The CRM structure I’ve been trying to migrate to contains a large number of custom fields.  The DMF calls many stored procs to get its data (from the CDF database); these are structured by entity e.g


p_dm_GetContact
p_dm_GetContract


I was having a problem with Contacts, and after tracing a migration attempt with cut-down data (for speed), I found that the issue was that the lid was being blown off a VARCHAR(8000) in p_dm_GetContact giving the following SQL Error:


Error: 207, Severity: 16, State: 3


The error is ‘Invalid Column Name’.  Further investigation revealed that a slightly dodgy piece of code (replicated in all the other similar procs) effectively truncates the dynamically created field list in the ‘SELECT’ statement…


— if there are extended fields, then attached them to the end 
—   of the base field list

if len(@metadatafields) > 0
    set @entityfields = @entityfields + ‘, ‘ + rtrim(@metadatafields)


@entityfields contains a comma delimited list of fields from the ‘cdf_Contact’ table, and @metadatafields contains fields from ‘cdf_Contact_Ext’ (custom fields).  If you concatenate the two you’re obviously asking for trouble.


The following shows the ‘fix’ to enable a bit more room (it’s still not very good – but as the code’s not available we can’t make a nicer fix right now)


CREATE procedure p_dm_GetContact
as
begin
    set nocount on
    
    declare 
@entity            varchar(100
)
    
declare @entityfields     varchar(8000
)
    
declare @metadatafields varchar(8000
)
    
— set target entity name
    
set @entity 
‘Contact’

    
— query base field list for targeted entity
    
exec p_dm_GetFields @entity, ‘c’, @entityfields 
output

    
— query extended field list for targeted entity
    
exec p_dm_GetMetaDataFields @entity, ‘ext’, @metadatafields 
output

    
— if there are fields, then attached them to the end 
        —   of the base field list    
    
if len(@entityfields) > 
0
       
set @entityfields ‘, ‘ rtrim
(@entityfields)

    
— if there are extended fields, then attached them to the end 
        —   of the base field list
——    if len(@metadatafields) > 0
——        set @entityfields = @entityfields + ‘, ‘ + rtrim(@metadatafields)

    
if len(@metadatafields) > 0
        
set @metadatafields ‘, ‘ rtrim
(@metadatafields)

    
— build/execute query statement
    — the status code must be null
    
exec (
‘    select top 1 ”” As CRMUpdateStatus, 
            c.ContactId As entityid,
            c.StateCode As statecode,
            dbo.p_fn_GetID(l.DestinationId) As originatingleadid, 
            dbo.p_fn_GetID(p.DestinationId) As defaultpriceLevelid, 
            dbo.p_fn_GetID(a.DestinationId) As accountid, 
            dbo.p_fn_GetID(su.DestinationId) As owninguser 
            ‘ 
+ @entityfields + @metadatafields + 


                 from cdf_Contact c

       inner join cdf_Contact_info info 
                   on c.ContactId = info.ContactId

       left outer join cdf_Contact_ext ext 
                   on c.ContactId = ext.ContactId

      left outer join cdf_Lead_info l 
                   on c.OriginatingLeadId = l.LeadId

      left outer join cdf_PriceLevel_info p 
                   on c.DefaultPriceLevelId = p.PriceLevelId

      left outer join cdf_Account_info a 
                   on c.AccountId = a.AccountId

           inner join cdf_SystemUser_info su 
                   on c.OwningUser = su.SystemUserId

           where (info.StatusCode is null)
         and (info.DestinationId is null)’
)
end
GO



Colorized by: CarlosAg.CodeColorizer