Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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!

share|improve this question
add comment

7 Answers

up vote 12 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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)
share|improve this answer
2  
In an effort of being balanced here, the OP asked about SQL, and for SQL you can manage the BDC model in SPD (don't necessarily have to use Visual Studio). You also have the option of something like BCS Meta Man from Lighting Tools to rapidly accelerate and minimize the effort in Visual Studio to create models to other data sources that aren't supported by SPD. BDC is VERY powerful, sometimes there are scenarios where it won't work (your InfoPath issue being an example), but I wouldn't use that to steer someone away from BDC completely. –  webdes03 Feb 29 '12 at 15:21
    
Thanks for providing this information. Please do not misunderstand me. I am not saying not to use BCS - I am just trying to express my experiences with it. –  Bernd Rickenberg Mar 2 '12 at 12:50
add comment

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).

share|improve this answer
add comment

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

share|improve this answer
add comment

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

share|improve this answer
    
That's a bit silly. What is wrong with using Microsoft.SharePoint.BusinessData.Infrastructure; and then var fooVar = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { fooIDval });? –  Kirk Broadhurst May 21 '12 at 4:05
    
it gets sillier when the number of fooVars increases.. –  Supriyo SB Chatterjee May 21 '12 at 13:31
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.