Migrating the attached files from Notes in CRM using SCRIBE Insight to SQL Table

Posted On January 31, 2017 by Posted in

In CRM, when a file is attached in Notes sections, it gets stored in “Annotation” Entity. Notes with Files attached have the isDocument set to 1 and without notes to 0. Since Annotation entity contains files attached in all other CRM entities, it is determined by “ObjecttypeCode”. Example for Account ObjectTypeCode is 1. ObjectId field in Annotation holds the GUID of the related entity’s Primary Key whose file is attached. Example Accounts Accountid is ObjectId of Annotation.

When we create a CRM connection for Annotation, we get 2 fields as highlighted below “bodybinary” and “documentbody”.

When we migrate from CRM to SQL, we need to Map the source CRM bodybinary field to vfAttachment in Target. If you map to document body, the files gets migrated however when you open it, it looks like corrupted. (During CRM to CRM migration, ensure that both the fields objecttypecode and objectidtypecode are mapped, else migration will get fail.)

If we need to pull the attached files from CRM and insert the same in SQL Table and write the same files in to Windows folder, things get bit tricky here.

While creating the SQL Table what will be the datatype for column that will be storing the attached file? The datatype you need to select is as “Image”. If you select longvarchar or text or binary, when the file is written on disk and open, you get a gibberish file with unreadable content.

Please note even though the Data type of the SQL Table for DocumentBody is defined as “Image”, Scribe shows the datatype as “binary”. Also during mapping it is advised to map the documentbody field at the very end. We had encountered this issues of files getting corrupted and Scribe support team suggested this approach and it worked.