Microsoft Dynamics CRM is a great platform for storing hundreds of thousands, or even millions of records, but not so much for file attachments. SharePoint, on the other hand, is a proper document management system, and makes a great candidate for storing file attachments as it natively integrates with CRM, and in an O365/online scenario, offers a significantly more cost-effective storage option.

In this post, I will demonstrate how to use SQL Server Integration Services (SSIS) as an Extract Transform Load (ETL) platform to extract CRM record file attachments and load them as documents in SharePoint. I will use KingswaySoft SSIS Toolkit for CRM and KingswaySoft SSIS Toolkit for SharePoint as add-ons to assist with the reading and writing of data from and to CRM and SharePoint. You can use this approach for the purpose of one-off or periodically archive CRM record file attachments as SharePoint documents.

archive file attachments kingswaysoft

The first (#1) step is to use KingswaySoft SSIS Toolkit for CRM to extract the file attachments from CRM, and store them in a staging database. I don’t recommend going directly from CRM to SP. I recommend the use of a staging database so you can easily segregate, execute and validate your ETL operations – extract, transform, and load.

In this example, use “FetchXML” as the Source Type to retrieve account file attachments.

archive file attachments kingswaysoft 2

 

The staging database has a table with the following definition. Take note that the content of the file attachment is stored in a column called “documentbody” with type nvarchar(MAX).

staging database

 

You may perform any transformation steps at this stage as necessary — perhaps creating as records in the staging database the corresponding Document Location entries in CRM to link to the new document location.

The second (#2) step is to load the file attachments from the staging database into SharePoint. The trick in this approach is the data conversion step in between, which is about converting documentbody from NVARCHAR to Bytes.

nvarachar

 

Finally, simply map to the document library in SharePoint.

sharepoint document library

 

sharepoint destination editor

 

There you have it, a simple approach to effectively move or periodically archive file attachments from CRM records to SharePoint. You may have to extend on the example above to handle exceptions such as to create the corresponding Document Location records in CRM to link to the document location in CRM if it doesn’t already exist, what to do if the file already exists in SharePoint, or if CRM or SP is inaccessible, etc.

External links: