CEDANIEL.COM

This post applies to Microsoft Office SharePoint Server 2007 and WSS 3.0

Scenario: In many cases SharePoint Lists can grow to be 1000s of rows. Often, you are required to build a report of some kind based upon the data being collected in the SharePoint list. It is an easy solution to build custom views with SharePoint services or SharePoint Designer to render the data in the form of a report. However, I have noticed that once the data grows to a large number, a more advanced approach is needed to aggregate the data with a method that is optimal for performance. When the list grows to 1000s of rows, views tend to be more difficult and perform poorly (depending on what you are trying to display).

Solution: Initially, I started to experiment with getting data out of a SharePoint List via MS Access 2007. There is a great tool that is available via MS Access that allows you to do this and may be optimal for your solution. However, in my experience the performance of MS Access begins to decline greatly once you get data from a list in the numbers of 1000s. It also seems difficult for Access 2007 to accomplish this task across a network when retrieving 1000s of rows of data.

There are also some custom solutions that can be achieved by building a web application that pulls data from a list by programming against SharePoint services. However, in most cases I found this to be just too much trouble for such a simple task.

What I have successfully used and recommend is SQL Server Integration Services. This solution will work for both SQL Server 2005 (SP 2) and SQL Server 2008. Microsoft has released a tool that can be installed and used with the Business Intelligence Studio that comes with SQL Server 2005/2008. This tool installs two additional options that can be used when building an Integration Services Package. It allows you to pull data from a list and send it to SQL server – OR – take data from SQL server and send it to a SharePoint List. It is very effective.

Read this post from MSDN for instructions: http://msdn.microsoft.com/en-us/library/dd365137.aspx

Go here to download the .msi files (You will see the 2005 adapter available for download): http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=17652

Additionally, using this package I was successful in pulling data from a SharePoint List and Importing it into a MySQL database running on Mac OSX server. To do this, you will need to install the MySQL ODBC or ADO.net drivers onto the SQL server box. I may create a separate post specifically on how to do this.