Database to SharePoint List Synchronization Tools

MS: Out of the box

• BCS (Business Connectivity Services) External Content Types
• This is less synchronization than it is a SharePoint interface to the Database
• There are limitations on using External Data columns within SharePoint and with Office and third party integration

Third Party COTS Synchronization

• Layer 2: Business Data Connector
• BoostSolutions: SharePoint Data Connector
• Simego: Data Synchronization Studio

BCS BDC external data lookup field migration

Scenario

• You want to programmatically set an External Data lookup field
• Ex: Migrating data into a SharePoint list or library

Issue

• In this case, we used Idera / Metalogix Migration Manager, which does not support External Data fields, nor do they plan on supporting it in the near future
• This is likely due to the fact that they offer a client only (non-server deployed solution) and haven't figured out how to do this using the remote content deployment APIs
• The result was that the external data field only displayed correctly in the View Item form and did not populate in the Edit item form or any of the related/projected fields in the view.

Resolution

• Luckily I did have access to deploy a server solution, so I developed the following web part to handle the migration
• If you are using Office 365 or don't have this access then you will need to develop your own solution or need to use a data synchronization solution instead (several vendors make these)
• In order for this solution to work, you need to add a temporary column to the list that will store the IDs of the field you wish to migrate.  Note that this is not necessarily the display name.
• In our case we migrated the ID column to a list column named SourceId, ran the tool, then deleted the SourceId column.
• I utilized code from StackExchange and Jaspers' Weblog along with some forum posts, converting all to VB.Net for aesthetics
  
• Download the code
• The wsp solution file is located in the \SetExternalDataWebPart\bin\Release folder
• You must enable the feature at the site collection level and add the web part to a page in the subsite that contains the list. 
• In my test environment this tool updated 25 records every 10 seconds.
• Notes
• I found that in addition to setting the display name you also need to set the RelatedField to the BDCIdentity to get the correct value to display in the edit form and to be updateable via the refresh icon.

// Set the BCS field itself (Display Value)
listItem[dataField.Id] = dtBDCData.Rows[0][dataField.BdcFieldName].ToString();
// Set the related field to the BDC Identity
listItem[dataField.RelatedField] = dtBDCData.Rows[0]["BdcIdentity"].ToString();

• Some other tips:
• I had to use the RevertToSelf (BDC Identity) connection to get the refresh to function
• I did not need to run with elevated or allowunsafeupdates except when debugging permissions.
• Updated release info:
• 6/27/2013
• Added list ID range support to split copies into smaller chunks
• Uses an SPQuery on ID field.
• Added refresh interval for regular mid-copy status updates
• These are actually just auto post backs using an asp.net timer control
• 7/5/2013
• Fixed refresh bug