Migrate Views through SSIS in Dynamics CRM

Introduction:

We can migrate both System Views as well as Personal Views through SSIS in Microsoft Dynamics CRM. Before we look at how this can be done through SSIS, let’s see what System and Personal Views are.

• System Views:System Views are those views, similar to Public views which are automatically created by Dynamics CRM on installation. System Views include Associated View, Advanced Find View, Lookup View and Quick Find View.
• Personal Views:Personal Views are those views, which users can create on their own by using the Advanced Find tool and save them as personal views.

While system views are stored in the database in the ‘SavedQuery’ table, the Personal Views are stored in ‘UserQuery’ table. So while querying to the database to fetch the System as well as Personal Views you have to execute the query for these two tables.

Things to be taken care of while migrating views:

We can’t migrate views directly because there are a few fields which needs to be replaced before migrating views. These field are as follows.

• ReturnedTypeCode
• LayoutXML

Let’s see what changes need to done in the data of these two columns.

ReturnedTypeCode: While migrating views for custom entities we observed that ‘ReturnedTypeCode’ field which contains Entity Type Code of Entity is having different integer values in Source CRM as well as in Target CRM. As we know when the entity is created, a unique integer value, Entity Type Code is assigned to every entity. So while migrating the data associated with the custom entities a custom code needs to be written in our Dynamics CRM migration tool to set ‘ReturnedTypeCode’ for these records.

Here’s the code snippet.

// Checks if ReturnedTypeCode is not null
if (!Row.ReturnedTypeCode_IsNull)
{
 //check if the ReturnedTypeCode is of Project entity 
 if (Row.ReturnedTypeCode == 10110)
 {
  // sets ReturnedTypeCode of Project Entity in Target CRM to the output column
  Row.ReturnedTypeCodeOp = 13024;
 }
}
else
 {
  Row.ReturnedTypeCodeOp = Row.ReturnedTypeCode;
 }

LayoutXML:

LayoutXML is a grid that displays results from the saved query. While migrating views there is a column named LayoutXML which contains a query of the resultset.

Below is the example of layoutXML.

Here we can see there is a column named object which contains EntityTypeCode of the Entity. We have to replace this object field’s value with EntityTypeCode of project entity in Target CRM. We need to handle this in custom code.

Here’s the code block.

   string layoutXML = string.Empty;
   if(!Row.LayoutXml_IsNull)
   {
   //read the Layout XML
   layoutXML = System.Text.Encoding.ASCII.GetString(Row.LayoutXml.GetBlobData(0, (int)Row.LayoutXml.Length));
   layoutXML = layoutXML.Replace("\0", "");
   if(!string.IsNullOrEmpty(layoutXML))
   {
  	 if (layoutXML.Contains("10110"))                  
             {
	       // replace object fields value with Target CRM entity type code
              layoutXML = layoutXML.Replace("10110", "13024");
              // set layoutXML value in output column
              Row.LayoutXMLOp = layoutXML;
              }
    }

Now we have to map the output column ‘ReturnedTypeCodeOp’ to the column ‘returnedtypecode’ field and ‘LayoutXMLOp’ to the ‘layoutxml’ in destination CRM as shown in the screenshot as below.

If we couldn’t map these fields as stated above while migrating views then it will cause an error in destination CRM.

Conclusion:

While migrating views for custom entities make sure you are replacing the value of entitytypecode according to the entitytypecode for the entities of target Dynamics  CRM for columns ‘layoutxml’ and ‘returnedtypecode’.

Opt in for Bing Maps and Dynamics CRM integration instant trials for free!