Integrating Sharepoint List data into SQL Server via SSIS Package

Have you ever wanted to be able to query your SharePoint lists from SQL Server? 

This seems to be a fairly common request so I wanted to demonstrate how to best accomplish this, this post was created using Visual Studio 2010 on a computer running SQL Server 2012, but will work back to SQL Server 2005. So by default your list of sources and destinations in an SSIS data flow task looks something like this, with variations between the versions. Notably absent is SharePoint...

So to remedy this, you COULD use a script task to write a web service call in a .NET language that pulls information from the SharePoint web service, if you have access and your SharePoint admin set it up correctly, however there is a much easier way. The SharePoint List adapter add-in for SSIS, available here on CodePlex . This is open source, so getting your IT dept to allow its use in the enterprise can take some work, but it is worth it. After a very quick install (follow the wizard...), your new source and destination options will include SharePoint Lists as shown below. I am not going into all the details of how to install this, codeplex covers all that already. If you have problems leave a comment and I'll try to help. MSDN agrees, this is the best way.

This is the result, just drag and drop these like any other source or destination in a data flow task. I have added some very helpful red arrows to illustrate whats going on here
.

jump to next post on using your list's GUID to avoid name change issues

UPDATE 10/17/2013

In yesterday's SQL PASS live stream Matt Masson mentioned in the SSIS talk that this SharePoint List adapter is the most popular codeplex item! So everyone who has been using this, you're doing it right. Until they release the OData source for SSIS and this is no longer needed of course...