31 Jan’17

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

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.

With above tips, you can ensure successful migration of documents from CRM to SQL and later when writing to local drives.

Written by

Team Member


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Managing mailbox through Email Archiving

    20 March’ 2018

    Introduction: Email can fill your Outlook Inbox quickly – new messages, replies, and forwards. Before you know it, yo...

    Read more
  • Workaround to Report.SAVEASPDF in NAV 2018

    20 March’ 2018

    Objective: In NAV 2017 Emailing the PDF by running the report using Report.SAVEASPDF is now not allowed in NAV 2018. Th...

    Read more
  • Dynamics 365 Client Diagnostics

    15 March’ 2018

    Main cause of Performance Issues: Bandwidth and latency are the primary characteristics which affects the performance o...

    Read more
  • Closing Blind Shift in MPOS and CPOS in Dynamics 365 for Retail

    15 March’ 2018

    Manual For closing Blind shift in POS: Blind shifts terminate the currently ongoing shift and when logged in a new shif...

    Read more
  • Persistent Filters in the Power BI Service

    13 March’ 2018

    Introduction: The feature is finally release, and it is power BI has announced general availability of persistent filt...

    Read more