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…..