How to sync a SQL database with a SharePoint List?

Question

I need to sync an already existing SQL Database to a SharePoint 2010 list. Two way sync would be great but one way sync is also alright. Any documentation in this regard would be of a great help. Thanks!

Answer 1

Business Connectivity Services (BCS) is the way to go...here's a nice 3 part series on setting it up as well as programatically working with it:

http://www.zimmergren.net/archive/2010/01/18/sp-2010-getting-started-with-business-connectivity-services-bcs-in-sharepoint-2010.aspx

Tons of data out there if you want to do additional research...google SharePoint 2010 BCS.

Answer 2

There is no need to sync the SQL table and SharePoint list - instead use Business Connectivity Services (BCS) and create an external list. Read more here: http://msdn.microsoft.com/en-us/library/ee558778.aspx

Answer 3

In case you do not want to use BCS (I am personally not a fan of BCS) there are some third party alternatives. This component from Layer2 for example is very easy to use but maybe best suited for lists without a feature based content type: http://www.layer2.de/en/products/Pages/SharePoint-Business-Data-List-Connector.aspx

In my opinion BCS has these drawbacks:

• BCS lists crash once in a while after a re-deployment, which requires either to re-activate the external list/content type feature and sometimes even to delete the list instance and re-create it
• Creating and maintaining BDC models in Visual Studio is not straight forward and can be quite time consuming
• InfoPath display and edit forms for external lists cannot be deployed with an WSP solution package (at least we did not find a way to do this)

Answer 4

We find and install Data Synchronisation studio from this link at our company

http://www.simego.com/Products/Data-Synchronisation-Studio

and this tool saved my life and a lot of time. You can connect it to almost everything that contain database via Your or custom account, setup automatic synchronization tasks or simply export data to other aplication like excel. I can setup regular tasks from AD to list or MS SQL to list in two minutes.

(It is a third party tool and maybe this sounds like advertisment, but it isn't (and I'm not an employee either) and if admins decide to delete this post, I understand).

Answer 5

Just see the video, how easy SharePoint data integration can be with 3rd party. It take 5 minutes to integrate a SQL query with all list features, e.g. workflows on external data change:

http://www.youtube.com/watch?v=tdNO5y14LSc

Answer 6

It gets uncomfortable when you need to dig into BCS coding, for example this line of code to retrieve a BCS field -

var fooVar = Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { fooIDval });

There's a new CodePlex project that looks more promising by using SQL Server Integration Services - SharePoint Lists integration with SSIS

Answer 7

Depends on how complex and how many list to be updated.

For simple list, personally I prefer Powershell, retrieve the sharepoint list then connect your sql data center and update the tables accordingly.