1

I am working on an ETL project, and part of the requirements is to extract email attachments from MS Dynamics CRM 3.0 for migration into Salesforce.com.

The part where I'm stumped is in extracting (i.e., saving as files in a local folder), the documents which are stored as MS SQL 2005 "Text" (Base64) objects under the DocumentBody field.

If you're familiar with MS Dynamics architecture, these are stored under the "AnnotationsBase" table. Here's an example of a query and a couple rows of output:

SELECT top 1
   [AnnotationId]
  ,[OwningUser]
  ,[ObjectId]
  ,[Subject]
  ,[MimeType]
  ,[DocumentBody]
  ,[FileName]
FROM [Manufacturing_Company_MSCRM].[dbo].[AnnotationBase]
where isdocument = 1
order by filesize

AnnotationId: 02E5D4E3-5323-DC11-ADF9-0013726038EA

OwningUser: DC86BB76-0A74-DB11-8659-0013726038EA

ObjectId: ED0A7E57-4518-DC11-8EB4-0013726038EA

Subject: Note created on 03/31/2009 1:19 PM by Microsoft CRM

MimeType: text/plain

DocumentBody: Rmlyc3QgTmFtZSxMYXN0IE5hbWUsRS1tYWlsIEFkZHJlc3MNCkpvaG4sU21pdGgsanNtaXRoQG1pY3Jvc29mdC5jb20NCkZyYW5rLEpvbmVzLGZqb25lc0Bjb250b3NvLmNvbQ0KLCwNCiwsDQosLA0KLCwNCiwsDQosLA0KLCwNCiwsDQosLA0KLCwNCiwsDQo=

FileName: bmc_import.csv

With many different mime types and thousands of attachments, How could I script a way to populate a folder (let's call it "C:\attachments\") with these files (like c:\attachments\bmc_import.csv, c:\attachments\budget.xls, etc.)?

Thanks.

Yaaqov
  • 305
  • 1
  • 4
  • 14

1 Answers1

3

I have blogged an example of how to do this with web services in 2011. I realise this is a couple of versions ahead and is a different approach to a straight sql call but you might find it useful. I dont think the code would be vastly different in Crm 3, just how the service call is executed and the results parsed.

Exporting Attachments Mscrm 2011

James Wood
  • 17,286
  • 4
  • 46
  • 89
  • Could you clarify what language it is, what what kind of call you made to the database? It's not a SQL query. – Yaaqov Sep 12 '12 at 13:53
  • 1
    Its a C#, WCF Soap web service call. As a I mentioned in my post its not sql. – James Wood Sep 12 '12 at 14:07
  • Thanks. Do you know if T-SQL has an equivalent to your function here? byte[] data = convert.FromBase64String(e.Attributes["documentbody"].ToString()); – Yaaqov Sep 12 '12 at 17:15
  • 1
    This looks like it might do the trick. http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql – James Wood Sep 12 '12 at 19:12
  • You came across the same solution I did, so that confirms it. Thanks for your help. – Yaaqov Sep 12 '12 at 19:34
  • @Yaaqov I realize this is a long time ago but did you manage to export docs via SQL and if so any chance you could post the code? – keerz Jan 29 '19 at 18:19
  • @keerz Ha, this was 7 years of projects and companies ago, but James Woods' solution would be the best indication of what to do. As an aside, my ETL was Talend (an open source and Enterprise edition is available) – Yaaqov Mar 04 '19 at 21:34
  • @Yaaqov thanks. I used the James Woods method. Believe it or not, there are companies out there still on crm 2011!! – keerz Mar 05 '19 at 14:59