Extracting CRM Attachments with Ease

The attachment capability in Microsoft Dynamics 365/CRM is quite handy, it allows CRM users to upload files easily by attaching them to CRM records, which can later be viewed or downloaded from CRM UI. All is good, but the only concern is, when the CRM attachments are not well managed, it can easily become out of control, as the attachment take precious database space, and having large number of big attachment files in the CRM system could potentially slow down your general application performance. For CRM online organizations, this can be an even bigger issue when you have a large number of CRM attachments, as CRM online storage is primarily designed for application data rather than file storage, so adding more space to your CRM online database can be expensive.

With that being said, there may be many reasons that you might want to extract attachments from Microsoft Dynamics CRM. For CRM online users, one of the most appealing reasons, as we just mentioned, would be to outsource CRM attachments to a different cloud storage, such as SharePoint or OneDrive, which are generally a lot more cost-effective than using CRM online storage. In this blog post, I will show you how to extract CRM attachments and save them to your local file system using our CRM integration software - SSIS Integration Toolkit for Microsoft Dynamics 365 and SSIS Productivity Pack. SSIS Integration Toolkit for Microsoft Dynamics 365 is a easy-to-use, cost-effective, and high performance data integration solution for Microsoft Dynamics 365 software while the SSIS Productivity Pack offers many unique SSIS components that make your life easier as an SSIS/ETL developer.

To extract data from Microsoft Dynamics CRM, you typically start from adding a CRM source component to your data flow task in the SSIS package that you have. Within the CRM source component, you would be reading data from annotation entity for CRM note attachment records. In case you need to work with CRM email attachment, you should be looking at the activitymimeattachment entity. In our case, we use a FetchXML query to read from CRM server by including only those necessary fields, and we have also added a filter in the query so it only returns those annotation records that actually have an attachment instead of being just a plain note, so we check to make sure isdocument is true. The following is the FetchXML query that we use in our CRM source component.

<fetch mapping="logical">
   <entity name="annotation">
      <attribute name="annotationid" />
      <attribute name="filename" />
      <attribute name="documentbody" />
      <attribute name="filesize" />
      <attribute name="mimetype" />
      <attribute name="objectid" />
      <attribute name="objecttypecode" />
      <attribute name="objecttypecodename" />
      <filter>
         <condition attribute="isdocument" operator="eq" value="true" />
      </filter>
   </entity>
</fetch>

The CRM documentbody field stores file content using a base-64 encoded string. Hence, our next step would be adding a Premium Derived Column component which takes the documentbody field as the input and use the DecodeBase64 function to decode the file content, so the output column should be a DT_IMAGE field, which contains the binary content of the attachment. The following is the screenshot of the Premium Derived Column component.

Our last step would be adding another Premium Derived Column component that uses the WriteBinaryContent method to write the file content to local file system. The following is the screenshot that shows how to use the method to write file content locally.

I have also prepared a sample package which you can download for reference. The package is prepared in SSIS 2008 R2 format, it should automatically upgrade if you are using a newer version of SSIS (to work with Microsoft Dynamics 365 online CRM instance, we recommend you use SSIS 2012 or later). Note that you need to have the following two software solutions installed when opening the package.

• SSIS Integration Toolkit for Microsoft Dynamics 365 
• SSIS Productivity Pack

The entire data flow looks like the following.

In the sample package, I have also showed how to upload file contents from local file system to CRM annotation entity (data flow shown as below).

A few closing notes before we conclude the blog post.

• SSIS Integration Toolkit for Microsoft Dynamics 365 has made it easy to extract attachment data from Microsoft Dynamics 365/CRM.
• SSIS Productivity Pack is a premium collection of advanced SSIS components that can help you perform some advanced data transformation and manipulation functionalities which can otherwise require custom coding. The primary goals of SSIS Productivity Pack are to help you become an empowered SSIS/ETL developer that can do more with less effort.
• In the "Download CRM Attachments" data flow, I have shown the extraction using two Premium Derived Column components, which can be easily merged into one if necessary. The second destination component can also be replaced by a SharePoint, OneDrive or Dropbox destination component, so you write the attachment files to any of those mentioned cloud storage systems directly.
• I have used the Data Spawner component to generate the sample data in the "Upload CRM Attachments" data flow for demonstration purpose, you would typically replace that with your own data source component.
• Using SQL Server scheduling capabilities through the use of SQL Server agent job, you could easily automate CRM attachment extraction on a scheduled basis if you ever need to do so. After the extraction is done, you could potentially remove those attachments from your CRM system to save your precious database storage.

I hope this is helpful.