In my opinion, the best (and easiest) way to accomplish this goal is using SSIS packages and a component from Codeplex called Sharepoint List Source and Destination. Ray Barley wrote a tip regarding this component a while back and he explained how to extract data from Sharepoint. I encourage everyone to check out his tip as it explains how to install the component and has some very good tips regarding the Sharepoint Source task. In this post, we’ll go over the Destination task in more detail.
Setup a Sharepoint List
For this tip, I’m just going to set up a quick custom list. This example is done in Sharepoint 2007, but Sharepoint 2010 should be close to the same.
Go to Site Actions, Create:
Custom Lists, Custom List:
For this example, I’ll create a list called SQL Versions:
Once I have a list created, I need to create a view with custom columns that match my SQL query. For this example I need Server Name, Instance Name, and Build. To create a view click Settings, Create View:
Choose Standard View:
Name the View (for this example, I’ll name it SQLVersionView and make it my default view):
Next we’ll need to create custom columns. Go to Settings, List Settings:
To create the first column click Title and rename it to Server Name:
