Data Migration using OOB data import

I agree that CRM out of box data import function is not sophisticate relatively to other migration tools such as SSIS, scribe and many others. But it is hard to decide whether you have to buy a tool when you only need to relatively small amount of data to be migrated. And data structure also considerably simple.

Personally, I agreed to our management that we don't really need buy expensive migration tool when we are migrating a few thousands of records. Beside we also want to change the data structure and migration project seems like revamp of existing system's design.

But I strongly suggest that if you are migrating complex data structure such as a lot of many to many relationships and/or large amount of data. Please try to use any suitable data migration tool. (Of course, you pay for the price)

So below is the way we decide to migrate the data from CRM 4 to CRM 2013 or 2015.

Let's use contact entity as an example. In contact entity there's one look up attribute called Nationality. So Nationality and Contact has one to many relationship.

1. Create Nationality entity in CRM 2013/15. (Assume nationality entity only has name attribute.)
2. Download Nationality entity data import template from CRM 2013/15.
   Settings > Data Management > Download Data Import Template
3. Open Nationality.xls data import template in excel. (Right click > open with excel or drag and drop to excel. Do not double click.)
4. Extract data from source CRM 4 filtered view in SQL Management Studio.
   select new_nationalityid, new_name from filterednew_nationality
5. In Nationality.xls add one column before column 1 and give header name as entity display name(In our case is Nationality).
6. By doing this we are re-using CRM 4 GUID in CRM 2013/15. It will help us a lot while we are migrating lookup data.
7. Copy data from SQL query in step 4 and past values (without header) in opened Nationality.xls.
8. Save the file and upload into CRM 2013/15.
   Setting > Data Management > Data Import

1. Create custom attributes in target environment (CRM 2013/15).
   Note: Create option set names same as original system. E.g. Salutation
2. Create form and place necessary attributes on the form.
3. Download data import template from CRM 2013/15.S
4. Assume that import template file only contains 4 columns which are First Name, Last Name, Salutation, Nationality.
   Salutation is option set (Mr, Mrs. Miss, Dr) and Nationality is the lookup type. 
5. Open Contact.xls data import template in excel and add one column as first column. Give header name as entity display name (Contact)
6. Extract data from CRM filtered view by using SQL query.
   - Text, Numeric (nothing to ambiguous)
   - Option Set (use name field from filtered view)
   - Looup (use GUID)
   select contactid, firstname, lastname, new_salutationidname, new_countryid
   from filteredcontact
7. Copy, past the result into Contact.xls and upload into CRM 2013/15.
   Settings > Data Management > Download Data Import Template

Here is conclusion for my long winded explanation:

• Create schema in target environment.
• Download data import template. 
• Import master entity first.
• Add primary key column in data import template use the same primary key from CRM.
  GUID from all entity. 
• Use name column while extracting data for option set data from source system.
  (using T-SQL)
• Use GUID column while extracting data for lookup data from source system.
  (using T-SQL)

Tips while using Excel

• Use R1C1 style in Excel.
  https://goo.gl/wYtWEx
• Use spacing remarks in sql query so that we can easily map between query and data import template columns. 
• Replace NULL text with blank in excel.

Assume data import template has 10 columns and when you open excel in R1C1 style you will see Column 1, Column 2, ..., Column 10.
R1C1 style will extremely helpful to map if we structure the data extraction script as shown below.

Select
   column 1,
   column 2,
   column 3,
   column 4,
   column 5,
--------------- 5 ---------------
   column 6,
   column 7,
   column 8,
   column 9,
   column 10
from filteredview

I hope that it will help you when you want to save money for your data migration.

P.S. Still finding out of box way to import many to many entity.