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!


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:


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

| improve this answer | |

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

| improve this answer | |

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

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


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

| improve this answer | |

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:


| improve this answer | |

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

| 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

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.

| improve this answer | |
  • Hi Shawn if prefer powershell too. Do you have a snipped code to share. Thanks in Advance – naijacoder Jul 7 '15 at 4:22

You can export your table easily to CSV file. From here, you can open the CSV file in Excel and export directly to a Sharepoint list https://support.office.com/en-us/article/Export-an-Excel-table-to-SharePoint-974544f9-94bc-4aa8-9159-97282d256dab

| improve this answer | |
  • this is a onetime only upload. it will not stay in sync. – Mark Aug 6 '19 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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