Reporting Services report from related data in SQL and a SharePointList via SSIS

This is an older project that I wanted to talk about. I worked on this last June. It was a longer post, so i procrastinated.

I wanted to create a “mash up” of data from a SharePoint List and a set of SQL tables. I tried several things, somehting that should be easy, was not (at least for me).

  • First I tried using SharePoint as a DatasSource in a SQL Server Reporting Services. That worked, but I could not figure out how to combine that with a different datasource in a report. Basically I could not figure out how to use “SQL Joins” between two different data sources in SSRS.
  • Second I decided to look into combining my data inside SQL server. If I can establish my relationships inside SQL, then the report would be easy. The most efficient way (AFAIK) would be to use a liked server to the SharePoint list. That way the data stays in the SharePoint list, and I am just querying it. I found several articles talking about ODBC or OLEDB connections to a SharePoint list. I just could not get it work. I saw another article strongly recommending against it (I can’t find that link right now). So I scrapped that idea.
  • I Finally settled on grabbing the data out of the list and putting into a SQL Table. I am not a SQL guru, and I could not figure out how to use a temp table with SSRS, so I ended just adding a table. This table would be an exact replica of the data in the SharePoint list (not many rows). The data in the table would be erased and then repopulated every X number of hours. The question was how to get the data into SQL.

I decided to use SQL Server Integration Services (SSIS) to pull the data from the SharePoint list. I found the add-in to SSIS that made it easy to get data from a SharePoint List – SharePointListAdaptersSetupForSqlServer2005.msi. I ended up with a SSIS control flow that looked like this:

The first part deletes the contents of the destination table, second is the DataFlow piece:

The Data Conversion piece changes a “double-precision float” to a “unicode string”. All this is put together and creates a dtsx file (LoadSPList.dtsx) that can be executed by a Scheduled task:

DTEXEC.EXE /FILE “C:\LoadSPList.dtsx” /CONNECTION “connectionName”;”\”Data Source=DBName;Initial Catalog=DBName;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\”” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING EWCDI

Finally, I just to created a report that contained a SQL query to relate the two different SQL tables.

Fun project, using three different technologies, SSIS, SQL and SharePoint.

, ,

2 Responses to Reporting Services report from related data in SQL and a SharePointList via SSIS

  1. Frederic Latour March 18, 2011 at 2:11 pm #

    Just to let you know that our product “Enesys RS Data Extension”, a custom data extension for Reporting Services, lets you exactly do that without having to export SharePoint data.
    We came up with an approach that makes it possible to join/aggregate multiple SharePoint lists and SQL Server data as well.
    This is a commercial product though.

  2. Thomas Trung Vo August 22, 2011 at 10:33 am #

    Make Report by Reporting Service with SharePoint List

Powered by WordPress. Designed by WooThemes