Merging Data into a SharePoint list using SSIS

Applies To: SharePoint, SSIS, SQL

I was recently tasked with creating a one way sync between some data in SQL and a SharePoint list. This can be done through an External Content Type and External Content List and works pretty well. You can find instructions for setting this up all over Google. I found some and set it up pretty quickly. Unfortunately, it wasn’t quite what they or I wanted. The interface was kinda clunky and caused some complications in terms of using the list as a lookup (specifically in a site column).

So, I did some more searching and found the SSIS SharePoint List Source and Destination Adapters available on Codeplex. The install is super simple (Needs to be done on both your development machine and the SSIS Server) and following the readme included you can get up to speed pretty quick. Assuming you have Business Intelligence Studio installed, it’s a simple matter of right-clicking on the toolbox and selecting Choose Items. In the SSIS Data Flow Components tab scroll down and add the SharePoint Source and Destination components (again more details can be found in the readme).

So… How do you make these work? I’m certainly no expert, but I have gotten them to work and it’s solved my problem. I ran into some issues that you can hopefully avoid by following my small example.

My goal was to take a table from SQL (could also be a view or a simple SELECT statement) and to have that be written to a SharePoint list on a daily basis. However, I am using the SharePoint list for lookups. This means that I want to be able to add any new rows, update existing rows and delete missing rows without changing the SharePoint IDs. This can be done, but it’s not as straightforward as you might hope.

Continue reading