SharePoint List Adapter in SSIS using GUID instead of name

So you have an SSIS package that pulls data from a SharePoint List, and it's working fine (It's not? start here), until...someone comes along and changes the name of the list, breaking your package and possibly failing the agent job that calls it...what to do?

You could just paste in the new name, but to fix this for good, you can use the GUID of the list to make sure that it does not happen again because the GUID remains constant even if the name changes.

In the properties of your SharePoint List source, in the name field, replace the text with the GUID of the list in question as shown below.

For anyone who doesn't have this installed yet, here is the link to the list adapter on codeplex

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