8

Edited: What is the easiest way to scrape extract SharePoint list data to a separate SQL Server table? One condition: you're in a work environment where you don't control the SQL Server behind the SharePoint Server, so you can't just pull from the UserData table.

Is there there any utilities that you can use to schedule a nightly extract?

Is Microsoft planning any improvement here for "SharePoint 4"?

Update Jan 06, 2009:
http://connectionstrings.com/sharepoint
For servers where office is not installed you will need:
this download

Rich Seller
  • 83,208
  • 23
  • 172
  • 177
BuddyJoe
  • 69,735
  • 114
  • 291
  • 466

12 Answers12

5

There is a SSIS SharePoint task you can use to grab the data info a regular dataflow: http://www.codeplex.com/SQLSrvIntegrationSrv

3

Scraping? As in screen scraping? Are you serious? ;)

2 Options

SharePoint Object Model - http://msdn.microsoft.com/en-us/library/ms441339.aspx

SharePoint Web Services - http://msdn.microsoft.com/en-us/library/ms479390.aspx specifically the Lists web service

The web services is how Excel/Access communicate with SharePoint to integrate with its lists.

In fact a bit of Google foo gives these two results :-

Connecting SQL Reporting Services to a SharePoint List

Accessing SharePoint List Items with SQL Server 2005 Reporting Services

Ryan
  • 23,871
  • 24
  • 86
  • 132
  • Actually depending on where your solution is deployed, you may be limited to only one of those 2 choices. While you can always use the web service option, the object model is only available if your solution is running on the sharepoint server. – Tundey Nov 21 '08 at 13:32
  • Good point - for many reasons the WebService is the better option (e.g. more scalable as it will use any web farm not single server) but the downside is that it isn't as rich as the object model. Since Excel/Access use it under the hood for there operation it should be rich enough though. – Ryan Nov 21 '08 at 15:27
  • Didn't mean screen scraping.... maybe bad choice of words. I meant data scraping, or pulling, or extracting. – BuddyJoe Nov 23 '08 at 17:50
  • Though you might Bruno, hence the smiley. WebSerices is the way to go – Ryan Nov 24 '08 at 09:06
3

There is an ADO.NET adapter for MOSS 2007/2010 and WSS 3.0/4.0 available which goes under the name Camelot .NET Connector for Microsoft SharePoint. It enables you to query lists in SharePoint through standard SQL language, using SharePoint as a data layer.

Besides from the connector, there will be a large number of open source tools and utilities available, such as webparts for exporting data to various formats (XML, MySQL, ..), Joomla plugins, synchronization services, etc.

See http://www.bendsoft.com for more details and to watch webcasts. BendSoft is currently looking for beta-testers and encourage all feedback from the community.

Example:

SELECT * FROM `My Custom SharePoint List`

INSERT INTO Calendar (EventDate,EndDate,Title,Location) VALUES ('2010-11-04 08:00:00','2010-11-04 10:00:00','Morning meeting with Leia','Starbucks')

DELETE FROM `Corp Images` WHERE `Image Name` = 'marketing.jpg'

NOTE: 20/04/2023

I've found a current version of this connector on the Nuget site... https://www.nuget.org/packages/Camelot.SharePointConnector

Paul
  • 4,160
  • 3
  • 30
  • 56
Ulf
  • 101
  • 2
  • Just found this after years of searching - @Ulf, you're a hero. – Paul Apr 20 '23 at 15:50
  • Note to any users looking at this - the BendSoft site is now gone, but the docco can be accessed using the [Wayback Machine](https://web.archive.org/web/20150718084125/http://bendsoft.com/documentation/camelot-net-connector/latest/). NOTE: You may need to adjust the date on the archive if you don't find what you're looking for. – Paul Apr 20 '23 at 15:58
2

I had written a full article about this with step by step screenshot procedures. It does not use any third party components only SQL BI Tools and Sharepoint. Have a look here

http://macaalay.com/2013/11/01/how-to-archive-sharepoint-list-items-to-sql-server/

Raymund
  • 7,684
  • 5
  • 45
  • 78
2

The 2 minute answer is to use Data Synchronisation Studio from Simego ( http://www.simego.com ) just point it at your List and database and it will sync all the changes.

  • 1
    Not bad if you have $749.95 to throw at a hole in Microsoft's strategy. Still wonder what the options for SharePoint 4 will be. – BuddyJoe Apr 20 '09 at 22:00
1

I would go with the simego software, but i dont have the money, maybe a 15 days trial is enough!

  • I guess I just think this should be standard. Included in the money you pay for SharePoint. The API to get this data out of SharePoint should be callable from a standard ODBC driver. – BuddyJoe Jun 19 '09 at 20:39
1

If you have MOSS installed, the Business Data Catalog can be setup from the Sharepoint Central Administration to automagically synchronize data for you. This is a very powerful product and is included with MOSS. I love it when a client has it enabled so I can take advantage of it.

But some don't and for myself, I've found that if they don't have BDC running and available, inevitably they don't give developers many rights to SQL Server so SSIS is generally out of the question (but maybe that's just me). No problem; for those I'll pull together a lightweight EXE that runs on a scheduled task that queries Lists.asmx and pushes changes to a SQL Server table. Fairly trivial stuff for a simple list where nothing is deleted. Get yourself Visual Studio 2008, CAML Builder, and prepare for a good time. The Lists.asmx results is a little funny in that a list's row's fields are each a single node with a lot of attributes, with no child nodes ... something like this off the top of my head ... just remember that when coding ...

 <z:row ows_Id="1" ows_Field1="A1" ows_Field2="B1">
 <z:row ows_Id="1" ows_Field1="A2" ows_Field2="B2">

Complications in code occur with copying lists where items are deleted, or where there is a parent/child relationship between SP lists. You'd think I'd have some code to send you, but I haven't bothered putting together something I could reuse.

I'm sure there's other ways of handling it, but the scheduled task EXE so far has been reliable for me for multiple apps for multiple years.

Paul
  • 4,160
  • 3
  • 30
  • 56
  • Thanks for the info. +1. In my situation (current company) BDC is off the table due to pricing. Wonder how this functionality will be packaged in the next SharePoint? – BuddyJoe Jul 06 '09 at 14:18
1

i wrote some code to achieve it, you can find it over here

extract data from moss 2007

user258880
  • 46
  • 5
1

As Ryan said I would also suggest using object model / web services to store data to separate SQL database. I think that the best approach is to write an event handler that will trigger on your least and copy the data user inserted/updated.

Toni Frankola
  • 1,652
  • 15
  • 26
1

Regarding your query about "SharePoint 4", Bill Gates made some remarks at SharePoint Conference 2008. He suggests enriching SQL tables with SharePoint data, and goes on to mention several other potentially cool things. What exactly he means and whether it will help solve your problem in the future is hard to say until we start seeing betas of WSS4 / MOSS 14.

Alex Angas
  • 59,219
  • 41
  • 137
  • 210
0

Depending on the exact nature of the data you need to insert, it may be possible to just use the auto generated RSS feed to get the information you want, a process will need to read the rss and formulate a query.

Otherwise a consoleapp/service could use the object model to do the same thing, but with more control over field information.

Nat
  • 14,175
  • 5
  • 41
  • 64
0

I wish something like this was much easier to do. Something that didn't need SSIS and was boiled down to a console tool that reads a xml config file for source/target/map info.

http://blogs.officezealot.com/mtblog/archive/2008/06/03/importing-list-data-into-sql.aspx

BuddyJoe
  • 69,735
  • 114
  • 291
  • 466