Exporting Note Attachnments / Annotations from Dynamics CRM 2015

Applies To

  • Microsoft Dynamics CRM 2015 Online
  • Microsoft Dynamics CRM 2015 Partner Hosted IFD
  • Microsoft Dynamics CRM 2015 On Premise - The process will be very similar but I've not tested

Client Request

I need to export and download documents I have uploaded and attached to the Notes entity in CRM 2015 Online. I need the files to be downloaded back to their original format. In Bulk.

The Issue

Getting a document back from a note in CRM is easy. Open the record, then open the note and click the link to get the file.

However, if you need to download a lot of them, it is going to take you a long time of clicking and opening to get them all.

Scenario

I have a client who works in the financial industry. Every so often they have an audit, where they need every document from a client, available to review. That means every single document and PDF needs to be exported into one folder for this client, so that the auditor can review them.

Related Information

  • The documents are stored in the SQL database as a binary BLOB.
  • They are stored in the Annotations table

Research

I spent over 30 hours, over a week looking for a solution. I thought I could pipe the data into an SSRS report and embed the files or somehow export the data from CRM, trying to use a beta edition of Scribe Software's File Stream addon - Nope.

Surely there must be an application out there to pull attachments. Well, yes, there is. During my scouring, I found two packaged options.

AttachmentExtractor for Microsoft Dynamics CRM

This tool looks like it would do the job - however whether there is any filtering on it for the records to limit what you get - i dont know. I didnt test the application, for the simple reason my client couldnt afford it.

Dynamics CRM 2011/13 Attachment and Entity data Extractor on Code Plex

I did try this, however I never actually got it working. I spent a few hours trying to get it to connect to my partner hosted CRM instance, but without success.

Solution

The solution I found (and assistance credit goes to Ray Murrell at Dominic Systems) involves a multi hop process using Azure, Scribe and Microsoft SQL Server. Sounds heavy, I know. I also need to give credit to the original "problem solution architect" over on Experts Exchange - Export CRM annotation attachments as files using classic VB

Some of these steps may be optional for you, if you already have them.

Solution Overview

Here's our end-game. We're going to use Scribe Software installed on a Microsoft Azure instance to connect to our CRM Online database and export the required attachments to a Microsoft SQL Express Database installed on the Azure server. Then use a funky VB Script to extract the documents binary, convert back into a real document and save to disk. When this has been completed, beer is optional.

Step One - Setup an Azure Instance

OK - it doesn't have to be an Azure instance. If you have your own Virtual Infrastructure, by all means, spin up a new VM. If you already have Scribe setup and installed then skip these steps and go use that. You could even install Scribe on your desktop/laptop. That's fine.

Step Two - Configure your MS SQL Express

Im using Express because I just need a SQL server to store the data temporarily and the express edition fits my needs.

Download and install the relevant SQL Express from Microsoft. By default picking this, has both the SQL Server and the SQL Studio management application.

You're going to use this for the Scribe installation too. TIP - you can skip this step entirely if you will be installing Scribe, as there is an option for SQL Install in the Scribe setup.

Step Three- Install and Setup Scribe on your New VM

Note - this requires a Scribe license. Hopefully you already have one or know someone with one. You can use the Scribe setup instructions, for a guide, but you can skip the parts about configuring message queues (if this is a test/quick install)

Step Three (And A Half)

You will need somewhere to put the exported documents. As I was doing a proof of concept and only need this for a short time, I just added a table to the SCRIBEINTERNAL database. You can add yours where you see fit.

Step Four- Configure your Scribe DTS for Export

Make a new DTS in the Workbench.

Connect to your Source - the SQL server.

Connect to the local SQL server for the Target.

Map the fields

NOTE: This screen shot came from the initial test - therefore table column names will not match.

NOTE: This screen shot came from the initial test - therefore table column names will not match.

Test The Transfer using the Test button and verify you are getting data

Run The Transfer and hopefully have no errors

Step Five - Add the Conversion Code

Now we need some code to convert the data we have and can easily access, back into a file stream when accessed. This function handles that for us on the SQL server.

The following code needs to be added as a function to the SQL server. Fair Credit again - this comes from the Experts forum.

Open the SQL Management Studio and select the correct Database

Run the following Script.

WARNING: This is the INTERNET. Do Not Trust This Script. Review it yourself before running someone else's code on your servers. I have run this code on my SQL server, but just because I did, doesn't mean you should. You have been warned.

CREATE FUNCTION [dbo].[DocBodyStream](@DocumentBody varchar(max))
--converts the DocumentBody field in CRM annotations table to a decoded stream
RETURNS varchar(max) AS 
BEGIN
DECLARE @decoded varbinary(max)
declare @stream varchar(max)
set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable(''@DocumentBody''))', 'varbinary(max)')
select @stream=CAST(@decoded as varchar(max))
RETURN @stream 
END

This will add the new function to convert the binary file data.

Step Six - Create the VB Script

This is the magic script. It will connect to the SQL server, grab the file data and then save each document in

Dim cn
Dim rS
Dim sql
Dim fsO
Dim outPath
Dim objSubF
Dim Stream
Dim fOut
Dim oID
Dim AnnID
Dim i
Dim msg

'creates a subfolder under outPath for each object_ID and writes any
'available filestreams for the object into the folder.Ignores annotations
'where documentbody is null.

outPath = "h:\CRM\Images"

Set rs = CreateObject("ADODB.Recordset")
set fsO = createobject("Scripting.FileSystemObject")

cn = "DRIVER={sql server};Server=myservername;database=MyCRMSample;uid=myUID;pwd=MyPwd;"
sql = "select AnnotationId, ObjectId, MimeType, FileName,  DocumentBody, dbo.docbodystream(documentbody) as FileStream "
sql = sql & " from annotations where documentbody is not null;"

rs.Open sql, cn

Do Until rS.EOF
If IsNull(rS("objectid")) Then oID="None" Else oID=CStr(rS("objectid"))
AnnID=rS("annotationid")
objSubF=outpath & "\" & oID'subfolder path for object ID
If fso.FolderExists(objSubF)=False Then fso.CreateFolder(objSubF)
fOut = objSubF & "\" & rs("FileName")
If fsO.FileExists(fOut)=True Then fsO.DeleteFile fOut
Set Stream=fsO.OpenTextFile(fOut,2,True)
With Stream
.Write rS("filestream") 'rS("documentbody")
.Close
End With
i=i+1
'msg - a list of the annotation IDs and files created with size information.
'intended for small test runs. I had only 21 populated annotations.
msg=msg & vbCrLf & "AnnID: " & AnnID & "(table/disk): "
 & "/" &  " FilePath: " & fOut
rS.MoveNext
Loop

rs.Close

WScript.Echo "done! " & i & " documents created." & vbCrLf & msg


Copy and paste the script into a text file and save it with the VBS extension.

Ensure that you update the connection credentials and the file output folder.

You can then run the script, using wscript.exe <SCRIPTNAME>.vbs from a command prompt. If all goes well, you should have a success message and files in your output folder.