Synchronize database with SharePoint list using SSIS

April 27th, 2011 | Posted by Jeffrey Paarhuis in Configuring

You ever wanted to show external data in SharePoint? Do Arabs fart in the desert? Does Apple charge 200 dollars extra for a different color macbook?

Of course you want to show external data in SharePoint. But it isn’t as easy as it seems to achieve this. There are a few methods and every methods has it’s pro’s and con’s.

According to Microsoft the best method, and every SharePoint guru will agree, is Business Connectivity Services. This method is great for creating a seamless connection between SharePoint and your external data. It however has the big disadvantage that BCS can only be used in conjuction with the external list, and external lists cannot have additional columns. This means that a calculated column needs to be custom developed in the BCS model, which in turn means that it needs to be compiled, tested, deployed, etc. And this is pretty stupid because SharePoint has calculated columns out of the box with a custom list. Now, I´ve tried to get that BCS data into a custom list, but with no success. So then I started looking for a way without BCS and use SQL Server Integration Services(SSIS) instead. After googling for a while I found exactly what I was looking for.

http://fsugeiger.blogspot.com/2010/01/synchronise-sql-table-with-sharepoint.html

This solution can synchronize a database with a custom list. It however is one-way, so customized data in the list doesn’t get back into the database. But because it’s a custom list we can use calculated and other additional columns.

Prerequisites for this SSIS solution.
You need to have SQL Server Standard or higher with Integration Services enabled and Business Intelligence Studio installed.
Then you need the following plugin for the SharePoint list source and destination.
http://msdn.microsoft.com/en-us/library/dd365137(v=sql.100).aspx

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

Leave a Reply

%d bloggers like this: