Importing Email into MS CRM 2011 via SSIS

Migrating data from a SugarCRM instance into a MS Dynamics CRM instance was mostly easier than expected. Microsoft has doing a good job of building some really useful upload tools in the out-of-the-box Import tool that allowed us to get most of the accounts, contacts, and activities moved over. But emails were another matter.

TL;DR

Use SSIS, make sure each CRM object that you want to link to is passed in as a string formatted as entitytype:{GUID};entitytype:{GUID}…. Coalesce is great as long as you don’t need to concatenate over 4000 characters of text.

issues with email transfers

Emails are complicated entities: they have arbitrary length lists in the to, cc, and bcc fields, and they are related to many entities at once. That’s a tough data model to cover, and the basic Importer tool just doesn’t have the sophistication to do it. So we had to look elsewhere for something that would help us import out email history from SugarCRM into Dynamics , and we found what we needed in SSIS and the Cozyroc SSIS plugin. But it was still a bear to get going, not in the least because there’s almost no documentation anywhere. Here’s how we did it.

General Setup

First, get your working environment set up. That means a full copy of each table you’re going to work with in a local database, so that you aren’t pulling your source data over the wire each time you need to reload your working set. For us, that meant a copy of the sugar tables for accounts, contacts, users, emails, email headers, and CRM tables for the same (via cozyroc). We also created a couple of temp tables that we copy our data into before manipulation, so that we’re never manipulating the original copy. Helps tremendously when you need to trace back an error to the original data. In SSIS, we set up the SQL commands for table drop and creates, as well as the copy of the data into those tables, as a sequence so that we could disable them all at once if we wanted to just work with the local data, skipping the (very long) reload time.

crux move 1 - linking emails to people

The first place we got stuck was figuring out how the CRM Odata interface wanted to receive the information about the contacts in the email headers. When we migrated our contacts into CRM, we brought over the source system ID as a custom field so that we could tie back if we needed to. That let us easily tie a CRM contact to a source system contact - highly recommended. But we tried sending every value we could think of to CRM - a contact’s name, a contact’s email address, the contact’s giud - as the header value of the email we were trying to import, and nothing worked. The email would get created in CRM, but the address field would just contain the raw text of what we were sending, not the linked CRM contact that we wanted.

With some searching, we came across a line on the cozyroc website that mentioned the proper structure:

entitytype:{GUID};entitytype:{GUID};.....

So to include a CRM Contact in the to: field of an email record we’re uploading, we would send

contact:{12345-12-33-567689abcdef}; 

to the to: field of the record during the cozyroc upload. (Just to be clear, the GUID is the guid of the CMR object that you want linked to the email.) Likewise, for an Account, you’d send account:{GUID}; and for a CRM user, you’d use systemuser:{GUID}. You separate each one by a semicolon. The entitytype comes from a list that Microsoft makes available here.

If you just want to pass in an email address that’s not tied to a CRM object, you can do that as well; just pass in the raw email address unquoted. Useful for keeping track of who was included on an email even if they don’t have a CRM contact.

Crux move 2 - Pivoting a multi- row email list to a semi-colon separated list - coalesce

Now we can poke a CRM object into email’s address field. Fantastic. Our second struggle was how to make the string containing each object that we wanted to include. Our source system stored a row per email address in each of the possible address fields; so an email with one email address in each of the to, cc, bcc, and from fields would have 4 database rows. WE needed to get those four rows pivoted into a single line, separated by a ’;’.

This sort of text pivot is a common SQL problem. The snippet we found on stack overflow was enough to get us in the right direction. We ended up writing a SQL function that we called when we were creating the address fields:

CREATE FUNCTION [dbo].[GetMatchedContacts] 
( 
	@emailID VARCHAR(100) 
	,@address_type varchar(32)
) 
RETURNS VARCHAR(8000) 
AS 
Begin
	DECLARE @CRMObjects VARCHAR(8000) 
	SELECT 
	@CRMObjects = COALESCE(@CRMObjects + ';', '') + crm_upload_value
	FROM crm.dbo.sugar_email_headers
	where address_type = @address_type
	and email_id = @emailID
	return @CRMObjects
End
GO

we called this function 5 times in our insert statement, one each for the to, cc, bcc, from, and sender fields in CRM.

Crux move 3 - 4k limit on COALESCE

Once this function was done, we through we were good to go - but when we started doing the inserts, we got an error that a GUID was misformed. After lots of digging, we discovered that the COALESCE function that we were relying on has a 4000 character limit. Once we filled up 4000 characters, it just truncated off anything past that, which was generally happening in the middle of a GUID.

The right answer to fix this issue would have been analyzing each email, and handling those that had over 4000 characters in their address string separately from those that didn’t. Because this was a one-time import, we just modified our function to only act on email addresses that matched a CRM record, and then created a copy of the function that only acted on emails that didn’t have a matching record. Then we concatenated the values of the two functions, and that was enough to reduce our largest emails down to less than 4000 characters per function.

Summary

This ended up being far more complicated than we expected. Getting CRM to take data from the Cozyroc SSIS interface was difficult, but doing it without Cozyroc would have been even harder. If anyone is trying to do this and would like some more guidance, drop me an email and I’m happy to give you the whole SSIS package as a template to follow.