Migration of Personal views/charts/dashboards with sharing information

In my previous blog I explained the issues that I faced while POA entity migration. Here I will explain the whole process that I followed to migrate the personal views with sharing information. I used kingsway soft adapter for my migration process.

We have 2 steps to migrate the data

  1. Source CRM –> Intermediate database.
    1. I enabled “Data Export Services” on the source instance to push the data into intermediate database.
    2. Since “userquery” entity is not available in “Data Export Services”, I Designed a simple SSIS Package with source (Dynamics CRM) and destination (OLE DB) components.
    3. We have to impersonate the owner of the personal view in the Dynamics CRM Source component to read this information.
    4. So we have to run this package multiple times based on the user count.
  1. Intermediate database –> Target CRM
    1. Designed 2 more SSIS Packages to push “userquery” and “principalobjectaccess” (share) information to Target CRM instance.
    2. userquery package is pretty straightforward with source (OLE DB) and destination (Dynamics CRM) components. So I am not explaining much about this package.
    3. But we faced some issues which I explained in the previous blog while migrating “principalobjectaccess” data. I followed the below process to migrate this data.
      • Created a SQL View in intermediate database on “principaobjectaccess” table by joining the “userquery” table to get the owner of the personal view.
      • I used this view in “OLE DB” source component to read the data.
      • This owner field should be mapped to “impersonateas” field in the destination component, so that internally the share request will be executed under owner context.
      • Batch size should be 1 in the Dynamics CRM Destination component, otherwise it will not process all the records.

This process is same to migrate personal charts (userqueryvisualization) and dashboards (userform) also. Hope this information is helpful.

Migration of POA (shares) of personal views/charts/dashboards

In our project we got a requirement to migrate shares of personal views. We all know that share information will be stored in “principalobjectaccess” table. I faced couple of issues while migrating this data and I want to share this information here.

Issue 1: You can’t share a personal view owned by other user. To solve this issue, Kingsway soft provided a field called “impersonateas” which should be mapped to owner of the personal view (you can get this information by join the userquery table with POA table). So that the share request will be executed under owner context.

Issue 2: After Issue 1 is fixed we faced another weird issue that the package is only processing only 1 share in target instance irrespective of no.of records from the source. After a bit of research, we identified this is because of the “Batch Size”, after changing the batch size to 1 with out any threads it processed all the records from the source. I hope it is because of the impersonation we are using in the package.

I hope this information helps some one who wants migrate sharing information of persona views/charts/dashboards.

Solved: Navigation property naming issue when working with Lookup fields using the WebAPI endpoint in CDS/CRM Connection Manager

In one of my previous post, I explained about the navigation property naming issue when working with Lookup fields using the WebAPI endpoint in “CDS/CRM Connection Manager”. Recently, I got a mail from Kingsway Soft support team with a Hotfix to resolve this issue. I installed this Hotfix in my machine and ran a simple package successfully to update custom lookup (account) on Contact entity.

This Hotfix is part of the most recent official release (20.1.0.1564). You can download this from the below link.

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download

So, I would recommend everyone to use WebAPI endpoint instead of SOAP in “CDS/CRM Connection Manager” due to the fact that SOAP endpoint is deprecated and no longer supported by Microsoft.

You can find all enhancements of the recent official release (20.1.0.1564) in the below link

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/change-log

Happy migration….

Performance issue while migrating activities

In this post I am going to explain how did I solved the performance issue while migrating activities using SSIS packages. My approach to migrate the data is, export the CRM data to Staging DB by using “Data Export” services and then to CRM by using SSIS packages. We all know that we have to migrate the party list (from,to,cc,bcc etc.,) data as part of activities migration and we need to write either SQL functions / script component to migrate party list data. I opted for SQL functions and I used those functions in SQL view. For ex, in email view I have to call 4 functions for each record to transform the data in party list fields (from, to, cc, bcc). So it leads to huge performance issue while fetching the data from staging table.

After a bit of research, I realised that I don’t have index for my intermediate tables. So I immediately created the following indexes.

  1. Index for “activityid” and “participationtype” fields on “activityparty” table
  2. Index for “activityid” on “email” table

and here is my SQL Function

CREATE function [dbo].[GetPartyListForEmail] (@activityid uniqueidentifier, @partyType int)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @partyList nvarchar(max);
	DECLARE @partyid as uniqueidentifier;
	DECLARE @logicalname as nvarchar(100);
	DECLARE @getPartyList nvarchar(max);
	 
	DECLARE db_cursor CURSOR FOR SELECT ap.PartyId,ap.partyid_entitytype 
	FROM email em 
	LEFT JOIN activityparty ap ON ap.ActivityId = em.ActivityId
	WHERE ap.ParticipationTypeMask = @partyType AND em.ActivityId = @activityid;

	OPEN db_cursor
	FETCH NEXT FROM db_cursor INTO @partyid, @logicalname
		WHILE @@FETCH_STATUS = 0
			BEGIN
				IF @logicalname = 'systemuser'
				BEGIN
					SET @partyid = (SELECT TargetUserId FROM mapping_systemuser WHERE SourceUserId = @partyid)
				END

				IF @partyid IS NOT NULL
				BEGIN
					SET @partyList = Concat(@partyList, @logicalname);
					SET @partyList = Concat(@partyList, ':');
					SET @partyList = Concat(@partyList, @partyid);
					SET @partyList = Concat(@partyList, ';');
				END
				FETCH NEXT FROM db_cursor INTO @partyid, @logicalname
			END

	IF (LEN(@partyList)) > 0  
	BEGIN
		SET @partyList= left (@partyList, len(@partyList)-1)
	END

	RETURN @partyList;
END

After adding the indexes, I experienced huge improvement and processed 230k records in 2 hours.

Hope it helps…..

Undeclared Property error while migrating lookup data using KingswaySoft adapter

I am using KingswaySoft adapter to migrate the data between MS CRM instances. I am using WebAPI endpoint in the connection string due to the fact that SOAP endpoint is deprecated. But I encountered an issue while writing the data into custom lookup fields and the package is failing with the below error.

An undeclared property ‘aw_accountid’ which only has property annotations in the payload but no property value was found in the payload. In OData, only declared navigation properties and declared named streams can be represented as properties without values.

Reason:

KingswaySoft adapter making WebAPI calls to create records in MS CRM since I selected WepAPI endpoint in my connection string. But with WebAPI calls, we have to pass entity schema name also to migrate the lookup data and KingswaySoft adapter has no option to pass this data.

Solution:

Unfortunately, there is no solution at the moment. I contacted KingswaySoft support team and this is the response I got it from them.

As you have already seen, the issue that you have experienced is a limitation on WebAPI, and from the cases we have seen it mainly happens to custom fields. This limitation is documented in our software. We recognize this is a blocking issue, so we recommend to not use WebAPI for the time being until the issue is addressed by Microsoft – we did highlight a number of other issues with WebAPI in our software tooltip in the connection manager. As of now, SOAP is still available and is highly reliable than WebAPI.

So, I solved this issue by changing the endpoint to SOAP in the connection string. Hope Microsoft is working on this issue and will solve asap.