1

I need to do some reporting across site collections. In order to do this I intend on writing a service layer that crawls SharePoint and puts information about document libraries and files into an SQL database. This would support SharePoint 2010 and SharePoint 2013.

What would be the best way to approach this? I've done it in a previous role using a Console Application on the server itself, but surely there's a better way?

Improve this question
  • Can't you take advantage of the search? It already has a nice index of most of the content in SharePoint – Robert Lindgren Sep 25 '13 at 6:13
  • @RobertLindgren I should have mentioned I'll be storing the data historically so I can do some trending analysis against it. I think this will rule search out? – Michael A Sep 25 '13 at 6:13
  • Yeah, there was the objective I was missing :) – Robert Lindgren Sep 25 '13 at 6:20
2

SSRS can be scheduled to run a report on your Site Collections, output them to CSV or similar and then use SQL to read the output and build a data cube. That could be a manageable, no/low code solution to build snapshot information for historic reporting.

Improve this answer
  • Doesn't really seem like the best approach though? In this case why wouldn't I just use a console application to do the same? Would seem more future proof with less overhead requried? – Michael A Sep 25 '13 at 6:23
  • The SSRS approach can be encapsulated within the business rules confines of the platform, is supported and quite flexible. Custom code has the overhead/reliance on maintenance by devs, differeing security requirements and so on. Always best practice to use the tools available first and then code only if the tools can't do it. IMHO. – Paul Leigh Sep 25 '13 at 8:34
1

You can use SSIS (SQL Server Integration Services) to Extract, Transform (if necessary), and Load (ETL) the SharePoint list data into SQL Server tables or tables.

There is an SSIS adapter for reading/writing to SharePoint Lists that can be found on CodePlex.

http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

You can schedule SSIS jobs with Windows Scheduler, the SQL Server Agent, or other methods (Systems Center Orchestrator, etc) to run periodically.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.