How to migrate files from MS SQL Server to Dynamics CRM Notes via Scribe
To be honest, I’m quite new to Scribe Software. I just recently started working with the product because I need to do a heavy migration for a project at work. The standard Microsoft Data Migration Manager was not an option so I started looking for other 3rd party products. That’s where Scribe came in the picture. I’m quite enthusiastic about the product. If you need convincing, send me an email or post a comment.
This blog post will discuss how to migrate binary files from a Microsoft SQL Server to Microsoft Dynamics CRM. The attachments, files, will be placed in the notes section of an entity.
First off, let’s look at the SQL Server.
This is what my database structure looks like:
CREATE TABLE [dbo].[Files](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FileName] [varchar](50) NOT NULL,
[FileData] [varbinary](max) NOT NULL
) ON [PRIMARY]
The actual file is, like you would guess, in the varbinary datatype.
In Scribe we want to connect to this table, this will be our source. Our target will be the Microsoft Dynamics system.
On the left you will see that I’m connected to the SQL Server. This is done via an ODBC connection that I have set up. On the right, I’m connected to the Annotation entity of the Microsoft Dynamics CRM system. Notes in CRM are stored in a separate entity and the relation is created by setting the object id to the GUID of the object where your notes need to be shown.
I will not dive into the details on how to setup a source or target. That’s not the purpose of this post.
On the bottom, in the data links section, you’ll see that I did a reference lookup in the internal Scribe database to locate a GUID of an organization migration which I had done previously. For this demo I want to attach the files from the database to the Notes section of my Account entity.
The actual file data needs to be stored in the vfAttachment attribute. The blob data will be stored here. The filename can be set optionally.
Another thing that you definitely should not forget to set is the objecttypecode attribute. If you forget this Scribe will complain with the following error message: CreateEntity failed: The parent’s object type is invalid.
Make sure to set it to one, using the formula button.
Once you are done, you will see the following when you want to test your DTS. The GUID is retrieved and the BLOB data will nicely be stored.
Run the job and your file will be attached to the Notes section of your entity:
