jan 21 anders jeppesen blog Microsoft CRM Microsoft CRM 2011 Scribe T-SQL

Issues when importing attachments with Scribe to CRM?

I became aware that when doing a normal Query against a SQL database with my binary attachment data like

SELECT * FROM ATTACHMENTS

It only took the first 128K of data from the column with the binary….that’s an issue when there is attachments in the size of 5+ MB :-).

The solution was to break up the SQL statement so instead of using * then define the fields you need so in my case it was:

SELECT ID, PARENTID, NAME, REGARDING_ENTITY, BODY

In that way I have placed all the binary data in the last column and then it will not cut off the binary due to the limitation of the SQL statement.

So for inspiration when I did a import from SalesForce on the email attachments there had around +20GB of data I’ve used the SQL statement below as a source Query (it’s combined with a view to do some SF magic):

SELECT     dbo.Attachment.Id, dbo.Attachment.ParentId, dbo.Attachment.Name,  ‘4202’ AS Regarding_Entity, dbo.Attachment.Body
FROM         dbo.Attachment WITH(NOLOCK) INNER JOIN
dbo.EmailMessage WITH(NOLOCK) ON dbo.Attachment.ParentId = dbo.EmailMessage.Id

For the target:

Normal insert step for the “activitymimeattachment” it’s a bit different compared to import annotations as you can have more that one attachment on a email, but it’s not an issue for Scribe to handle as long do the right lookup for the CRM guids.

The mapping came out like this, the adapter will deal with the rest of the fields you see in the target list so just make sure to do the 5 mappings and you should be good to go.

Import attachments with Scribe

Import attachments with Scribe

 

 

Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+