Lotus Notes to SharePoint Blog

Blog about Dell's Notes Migrator to SharePoint tool and other things related to Lotus Notes migration projects

Getting Started with Migrating to SQL Server Data Normalization: Images and Attachments

In yesterday’s article [link] I provided a walkthrough of migrating to a flat SQL Server table.   Now we will start digging into data normalization with related tabled.  Here we will look at using a One-To-Many table to store binary objects and in the next article we will examine Lookup tables.

Imagine that I designed a SQL Server database with a main “Documents” table and related “Parts” table for storing migrated images, attachments and embedded objects.  I wanted a separate table here because there is a one-to-many relationship between documents and images. 

The basic design for my database is shown below.  In addition to the BinaryData column (type VarBinary) to hold the raw binary data, I added DocID (int) and PartID (string) columns which together constitute a unique primary key.  Finally there is a PartName column (string) which stores the friendly display name for the attachments.   

image

Now in my Target Data Definition, I specify my Parts table as a “Normalized Table” with the relationship One-To-Many.  If you are lost at this point, go back and review the prior article “Getting Started with Migrating to SQL Server Databases”. 

image

In the Data Fields tab of my Target Data Definition, I need to define two columns for migrating content to.  (Unfortunately the “Load From SQL Server Database” button is not smart enough to do a complete job here, so you will have to do much of this manually.  If you press that button, make sure you review the following details carefully afterwards.) 

First I defined my DocId field for the Parts table and I set the “Link To Primary” property to True.  This tells Notes Migrator for SharePoint that the Primary Key of the Primary Table should always be copied to the DocId column of the Parts table.  This will happen regardless of whether the Primary Key was set from Notes data or was an auto-generated Identity column.

image

Next I defined a field of type File.  I called it “BinaryData” but I could have called it anything I wanted to.  Using the File Data Column, File Key Column, and File Name properties for my File field, I specified the three columns in my database described above for storing information about the migrated binary files.  The File Data Column property indicates where the actual binary data should go (the BinaryData column in my case).  The File Key Column property indicates where the unique attachment/image/object ID should go (PartID).  The File Name Column property indicates where the friendly display name should go (PartName).  Note that the File Name Column property is optional and the values written to the indicated column are not guaranteed to be unique.

image

Finally I set the Is Gating Field property for my File field to True.  This is very important, as it controls the normalization of records; if I map an array of 10 values to this field it will cause 10 records to be written to the Parts table. 

As shown below, I actually map all the attachments, images, and embedded objects to this field.  So if a Notes document had 6 attachments, 2 images and 3 embedded objects, we would get 11 new records in the Parts table.

image

After running my migration job, we can see that the DocID column values in the Documents table are automatically correlated with the DocID column values in the Parts table, and that it is not a one-to-one relationship.

image

One final question remains here… If all my images and attachments are stored in a separate table, how does the migrated rich text field (which is now an HTML field in the Documents table) refer to them?  I controlled this by using the “Generating Links to SQL Records” options on the Advanced tab of the migration job.  Here you can specify how links to images, attachments and even other documents should be formatted in the migrated rich text.  This feature is optional, and only makes sense if you have designed a web application to display the images and attachment files in response to a web request. 

image

Using the option shown above, the following attachment link and image reference tags were generated in my rich text document:

<a href=”http://quest.com/parts?server=REMGW0MTL1%5cSHAREPOINT&database=QDemo&table=Parts&id=2382&part=AttachmentLinks.docx”>

<img src=”http://quest.com/parts?server=REMGW0MTL1%5cSHAREPOINT&database=QDemo&table=Parts&id=2382&part=image0.GIF” border=”0″ />

2 responses to “Getting Started with Migrating to SQL Server Data Normalization: Images and Attachments

  1. roel ang September 21, 2011 at 6:21 pm

    my notes document has more than one attachment fields (@Command([EditInsertFileAttachment]), like;
    attachment_1
    attachment_2
    etc..
    how do i move them into sql server table like your body field example above.
    thanks
    roel

  2. roel ang September 21, 2011 at 6:40 pm

    got it! Allow Multiple Sources = True?