CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresdynamics-crm
Yaaqov
Yaaqov

Reputation: 305

Exporting Attachments from Dynamics 3.0 MSSQL Table to Local Folder

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.

Upvotes: 1

Views: 2479

Answers (1)

James Wood
James Wood

Reputation: 17562

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

Upvotes: 3

Related Questions