Wednesday, October 15, 2008

Data Migration Manager Tips and Tricks

Sonal Sawhney Published Tuesday, September 02, 2008


href="http://blogs.msdn.com/crm/archive/2008/01/04/microsoft-dynamics-crm-4-0-data-migration-manager-with-john-o-donnell.aspx">Data migration Manager (DMM) users who are looking for tips and tricks to improve their experience with DMM.

Boost Data Migration Manger’s (DMM) Performance

1. All the machines in the system must be in the same time zone and use same regional settings.

2. In the Internet Options -> Connections -> LAN Settings, set the physically nearest proxy server and set ‘Bypass proxy server for local addresses’. Do not use the option ‘Automatic Configuration’ as it involves in IL code generation (observed for .NET 2.0 SOAP classes).

3. Dynamics CRM 4.0 application has been launched at least once before testing. 

4. All data file size should be less than 32 MB. If you have a source data file that is over 32 MB, split the data into several files (making sure to include the header row in each file) and migrate them separately.

5. While running DMM, do not load the client with other applications.

6. Preferably do a Test run with lesser number of records before proceeding for actual Data Migration, so that you can undo the migration using DMM if any issue happens.

7. DMM will execute slow in a virtual machine (MS virtual server or Vmware) than a similar configuration real hardware.

8. Instead of using SQLExpress, if you have license to use SQLServer then it is better to use DM Client on the same machine where SQL Server is installed.

9. For an On-Premise Install, DMM and CRM server on different machines would enhance performance. If not, then make sure that the DMM and CRM server are on a fast LAN connection with no TCP routing hops or HTTP proxies in between.

10. The version of the Data Migration Manager must match the version of Microsoft Dynamics CRM to which you are connecting.

11. DMM cannot be run at the same time as Microsoft Dynamics CRM for Outlook. To disable CRM in Outlook :

In the notification area, click the Microsoft Dynamics CRM Application Host icon, and then click Disable CRM.  The icon appears grey.

Microsoft Dynamics CRM will be re-enabled the next time you start Outlook.

12. Turn off tracing/logging for better performance.

13. Upload the file IN ORDER of lookups resolution – if account contains lookups of contact then contact should be selected first and then account, this might save time in determining the order of files to be updated (sorting a sorted array takes less time)

Customize DMM

Can I customize Microsoft Dynamics CRM while DMM is running?

If you run a migration after making customizations in Microsoft Dynamics CRM, you might receive data upload errors. To prevent this, after any change in CRM metadata; user should restart Data Migration Manager to sync metadata customization.

In a particular case, when migrated data is for a custom entity and later using web App, if the custom entity is deleted, then Migration Purge using DMM tool will fail.

To succeed, create the deleted custom entity and retry deletion using DMM tool.

How to create attributes of Date-Time type through DMM?

DMM supports creation of Date type attributes. To migrate data in date-time attribute, create the date-time attribute on the server before beginning migration, and map the source attribute to the newly created attribute.

How to migrate Lookup attributes that Reference a Custom Entity Created by the DMM?

The Data Migration Manager does not support migrating records from other record types that refer to a custom entity that is created by the same migration. If you have other files in this migration with records that refer to this entity, you need to cancel this migration, and instead, do two migrations.

Step 1: Migrate this custom entity in the first migration, and the wizard will create it in Microsoft Dynamics CRM.

Step 2: Migrate data having lookup references that refer to this new entity.

When I use non-English characters in a name for a custom attribute, why do I get an "Invalid characters in name" error message?

DMM uses one input for both the database logical name and the display name, and the database logical name must use ASCII characters.

To work around this problem, after you migrate your data, use the Customization area of Microsoft

Dynamics CRM to change the display name.

When I migrate data to a custom attribute, if DMM fails to create the attribute, the first time, and I reuse the data map from this migration, why does my custom attribute data not get migrated?

If a custom attribute is not created the first time you run an Express mode migration, all data in this attribute will be ignored when you reuse the data map from this migration for another Express mode migration.

To work around this problem, when you start DMM using the data map, use Standard mode, so that you can specify creating a new attribute again.

Migrate Specific Attributes

Are you migrating Date/time format source data? Ensure this –

1. If the date/time format of your source data differs from the one set by you in CRM settings ,then consider changing the CRM format settings accordingly before starting migration. It is advisable to take a small number of test records and verify your expectations before starting migration.

2. Microsoft Dynamics CRM 4.0 Data Migration Manager validates the date data in your source data during migration. In cases where the date data is not in the format specified by your date format setting, DMM displays an error.

3. DMM understands the date time settings of the user in whose context it is running. To change the user timezone in CRM go to ToolsàOptions and change your time zone. To change the date format settings go to ToolsàOptionsàFormatsàCustomizeàDate, change and save the settings. These settings will be used by DMM to migrate the dates and times.

How can I preserve the auditing information like created on while migrating data from my CRM system to Microsoft CRM?

In order to migrate data into the “Created On” attribute, you must map your source column that contains this data to the Microsoft Dynamics CRM Record Created On (overridencreatedon) attribute. As the record is migrated, the Created On date will be updated with this value, and the Record Created On value will be set to the date and time the record was actually migrated.

However for a custom Entity created through DMM, data in the Created On column in the corresponding .Csv file cannot be migrated. To work around this limitation, create the custom entity using the Customization area of Microsoft Dynamics CRM, and then migrate data to the custom entity.

How can I migrate data into the Time Zone field?

For migrating the timezone fields, you need to specify the correct ‘code’ for that time zone in the .Csv file, the list of time zones with repective int values can be found in the DM help file.

How can I map state/status or any Boolean type field?

State/Status/Boolean fields are treated just like picklist fields in CRM and have one or more options associated with them. To migrate data into any of these types of fields, the user needs to define picklist mapping along with the column mapping specified.

E.g. Let’s say that the user wants to migrate data to “DoNotEmail” field of Account entity and the source system has a corresponding field by the name of “EmailAllowed” which takes “true”, “false”, and “not specified” as its possible values. The user will have to create picklist mappings mapping source system’s “true”, “false”, “not specified” values to target system’s Boolean options 0, 1 and 0 respectively. Same is required when a user is migrating data to state/status fields.

<AttributeMap Id="">

    <SourceAttributeName>EmailAllowed</SourceAttributeName>

    <TargetAttributeName>donotemail</TargetAttributeName>

    <ProcessCode>Process</ProcessCode>

    <PicklistMaps>

        <PicklistMap Id="">

            <SourceValue>true</SourceValue>

            <TargetValue>0</TargetValue>

            <ProcessCode>Process</ProcessCode>

        </PicklistMap>

        <PicklistMap Id="">

            <SourceValue>false</SourceValue>

            <TargetValue>1</TargetValue>

            <ProcessCode>Process</ProcessCode>

        </PicklistMap>

        <PicklistMap Id="">

            <SourceValue>not specified</SourceValue>

            <TargetValue>0</TargetValue>

            <ProcessCode>Process</ProcessCode>

        </PicklistMap>

    </PicklistMaps>

</AttributeMap>

How to overwrite pick-list mappings (different values) done by “Auto pick-list mapping”?

The DMM gives user an option to customize the CRM system automatically with picklist options that are unmapped. This option will take all the unmapped source picklist values and create pciklist options for them in CRM. The point to note here is that if the picklist value is unmapped only then the pciklist option be added to the CRM system. If the user does not want the CRM system to automatically customize the picklist options for an attribute, then she can create picklist mappings for that attribute mapping all the source picklist options to existing CRM picklist options.

Some records are migrated to Microsoft Dynamics CRM with a default status.

Documented at DMM ReadMe:

http://download.microsoft.com/download/e/f/b/efb71b2d-5b70-470a- 94a10260e460e437/Microsoft_Dynamics_CRM_4.0_Data_Migration_Manager_Readme.htm

How can I migrate data to the QuantitySellingOption drop-down list?

Quantity Selling Option is not customizable in Microsoft Dynamics CRM, so if your data has customized values in this column in your .csv file, it will not be migrated. To workaround this problem, you will need to do one of the following:

Manually edit the data map for the migration to map the customized drop-down list (picklist) values in your price list items .csv file to valid Microsoft Dynamics CRM values. The three valid values are No Control, Whole, and Whole and Fractional.

Edit your price list items .csv file and change the custom values to valid Microsoft Dynamics CRM values

Migrate Users

Why doesn't data from all the attributes in my users .csv file get migrated?

DMM will create new users based on data in a .csv file, but it only migrates the following fields to which you have mapped your source csv columns:

  • Domain logon name
  • First Name
  • Last Name
  • E-mail

You will need to manually enter any other user data.

How to map users and create new users?

Find the details at: http://blogs.msdn.com/crm/archive/2008/02/20/record-owner-information-migration-using-dmm.aspx

HANDLING ERRORS

Some of the records might have been migrated despite errors?

For an entity if the attribute customization fails from DMM, then the records for that entity gets migrated except for the column value for which the custom attribute was created.

In this case all the rows will have error as the custom attribute creation failed, but in reality all the records are migrated but only the custom attribute data is not migrated.

Fix this:

1. At the CRM Server side using Web App, user has to create the custom attribute and manually update all the migrated records with required value for this newly added attribute.

OR

2. Resolve all the reported errors in "Conversion error" screen before starting data Upload using DMM.

Encountered “Generic SQL error” during data conversion?

The most probable cause for this error is the field size limit per attribute. In case your attribute data exceeds the limit, opt for field width customizations by ensuring that the checkbox on the Overview screen of DMM which reads as “To accommodate the source data, automatically customize Microsoft Dynamics CRM list values and length of attributes” is checked. However attributes of type "Ntext", does not support automatic field width customization for attributes with length greater than > 2000. In case of failures, increase the field size by using CRM Application customization and retry to migrate the failed records after exporting the failed records from DMW.

Parsing error: “Row is too long to import"?

Currently there is a limit of 4000 characters on the columns of the data rows in the files used for migration. You will hit above error if any column size exceeds this limit. Here is a workaround:

1. On the machine where you have DMM installed, add a ‘Double’ type registry key called ‘ImportParsedColumnDefaultSize’ with a very large value (like Decimal 10000) under: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Data Migration Wizard

2. On the SQL server which you are using for DMM(or SQL express ) create a database trigger by running the following script:

   1: USE MSCRM_MIGRATION
   2: GO
   3: CREATE TRIGGER DDLConvertToMax ON DATABASE FOR CREATE_TABLE 
   4: AS 
   5:     DECLARE @raisedEventData XML 
   6:     DECLARE @substr XML
   7:     DECLARE @tablename nvarchar(500)
   8:     DECLARE @columnName nvarchar(100)
   9:     DECLARE @sqlstatement nvarchar(1000)
  10:  
  11:     SET @raisedEventData = eventdata()
  12:     SET @substr = @raisedEventData.query ('data(/EVENT_INSTANCE/ObjectName)')
  13:     SET @tablename = CONVERT(nvarchar(200), @substr)
  14:  
  15:     
  16:     declare @i int
  17:     SET @i = 0
  18:     while @i < 24
  19:     BEGIN
  20:         SET @columnName = 'COL' + CONVERT(nvarchar(50), @i)
  21:         if columnproperty(object_id(@tablename), @columnName, 'ColumnId') is not null
  22:         BEGIN
  23:             SET @sqlstatement = N'ALTER TABLE ' + @tablename + N' ALTER COLUMN '+ @columnName + N' nvarchar(max)'
  24:             PRINT @sqlstatement
  25:             EXECUTE sp_executesql @sqlstatement
  26:         END
  27:     SET @i=@i+1
  28:     END  
  29: GO  

Here is what the script does:

1. Operates on the DB, is independent of CRM code

2. Will convert all columns from COL0-COL24 automatically to nvarchar(max)

3. Will check for existence of columns before resizing

4. Operates on the MSCRM_MIGRATION Database

5. Consider running this only for the parse tables that are failing and then delete the trigger

Why do I get an error "An error has occurred? Please see the log file for more information." when I try to import a data map?

If you have a value that is too long in a parameter such as SourceName, Name, or SourceEntityName, you will receive this error. The log file will show a "Generic SQL error." To work around this problem, shorten parameter names in the data map to 160 or fewer characters.

Why do I get error messages about rows with a number greater than the number of rows in my .csv file?

Microsoft Office Excel doesn't display rows with blank data, so if you look at your .csv files with Excel, you will not see empty rows at the end.

To remove empty rows at the end of a .csv file, open it in Notepad.

Why do I see Data Upload Errors with no description?

If your record has a status of Published, it cannot be migrated. Articles must have a Status value of Draft or Unapproved in order to be migrated. After migrating the articles, use Microsoft Dynamics CRM to change the status of the articles.

For the following queries and many other refer Troubleshooting Guide

  • When I start Data Migration Manager, I see the message "Mandatory updates have not been installed. Data Migration Manager cannot continue.
  • Can't migrate data for entities (like business unit) which have mandatory self reference
  • How do I enable creating logs that include more details about migration errors?

References:

ReadMe:

http://download.microsoft.com/download/e/f/b/efb71b2d-5b70-470a- 94a10260e460e437/Microsoft_Dynamics_CRM_4.0_Data_Migration_Manager_Readme.htm

Troubleshooting Guide: http://rc.crm.dynamics.com/rc/regcont/en_us/OP/articles/migrationtroubleshooting.aspx

Cheers,

Sonal Sawhney

No comments: