Importing Attachments into Microsoft Dynamics CRM: A Challenge & A Solution

Recently I had to import Attachments from an external folder into Microsoft Dynamics CRM V4.  The source system did not store the files internally in the database, but used a path to point to files in a folder on the Server.  (This is true of Siebel CRM and earlier versions of GoldMine and Act!.)  All 45,000 files were stored in this folder. 

There are a couple of things to note when considering this type of import.  The first is that files greater than the size allowed by Microsoft Dynamics CRM will fail to import.  The second is that Email Attachments need to be handled differently than other Attachments.

In Microsoft Dynamics CRM, there is an ActivityPointer Table and several underlying tables that point to it.  One of these is the ActivityParty Table, which stores all of the links to an email address.  This is necessary because we can include many people in an email either in “To,” “CC,” or “BCC.”  Another underlying table is the Annotations Table, which allows notes to be attached to an activity.  The Annotations Table is where all Notes are stored in CRM.  There are also 6 Associated Tables, one for each of the 6 basic activity types:

  1. Appointment
  2. Email
  3. Fax
  4. Letter
  5. Phonecall
  6. Task

In most cases Attachments are not connected directly to an Activity.  They are stored as Notes and then the Notes are connected to the Attachments. So, to manually attach a file to a Task, you would create the Task, save it and then go to Actions/Attach a File.  The result would be stored in the Notes Tab in Microsoft Dynamics CRM 4.0* as shown below:   

CRM V4 

 *The same holds for Microsoft Dynamics CRM 2011

This is true for Appointments, Faxes, Letters and Phone Calls as well.  When importing into Microsoft Dynamics CRM using Scribe, you import the Attachment to the Annotation (Notes) Table.  This creates both a record in the Annotation Table and the Attachment in the ActivityMimeAttachment Table.  There are only 4 fields that need to be mapped to import the file successfully.  The vfAttachmentFileName field is the fully qualified path and file name of the attachment, such as \\Server\Folder\SubFolder\SAF.txt.

Scribe

But what happens to Email?  This was a problem facing a Client recently.  I brought in the Attachments for Emails with no errors, using Scribe.  But the Client noted that Attachments were missing.  When I opened a specific Email there was no Notes Tab and therefore no Attachment.  There was, however, an Attachments Tab as shown below:

CRM V4  

As it turns out, Attachments for Emails should not be imported through the Annotation Table, but should be imported directly to the ActivityMimeAttachment Table.  Below is a sample of the Scribe settings to successfully import Email Attachments:

Scribe

To summarize, Attachments for Emails are stored in this manner:
ActivityPointer –> Email –>  ActivityMimeAttachment

All other Attachments can be thought of as being stored in this manner:
ActivityPointer –>  Appointment –>  Annotation –>  ActivityMimeAttachment
ActivityPointer –>  Fax -> Annotation –>  ActivityMimeAttachment
ActivityPointer –>  Letter –>  Annotation –>  ActivityMimeAttachment
ActivityPointer –>  Phonecall -> Annotation –> ActivityMimeAttachment
ActivityPointer –>  Task –>  Annotation –>  ActivityMimeAttachment

Both the Appointment and the Annotation are children of ActivityPointer, but this illustration explains how Attachments are linked to Activities through Annotations. We have performed this import for several clients and the results have been solid.

1 thought on “Importing Attachments into Microsoft Dynamics CRM: A Challenge & A Solution”

  1. How does the attachment table figure into things?

    I have an import set up using Scribe and after everything runs, I see my single attachment attached to my email (as expected) but I have TWO records in the activitymimeattachment table and a single record in the attachment table.

    From what I see, the attachment table is where the document itself is stored - I see the "body" column which looks to contain the document. What I can't figure out is why the activitymimeattachment table has two records and not just one. Any ideas how this all ties together?

Comments are closed.

Show Buttons
Hide Buttons