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.