August 1, 2007 at 12:47 pm
Looking for a way to query Sharepoint lists from SQL Server. In so doing, I would be able to mix information from the lists with information I already have in a database (think: user-updateable parameters to control queries). Ultimately, the data will feed Reporting Services reports, but it is only useful if the Sharepoint list is combined with other data. Has anyone achieved this or have suggestions as to where to look?
Things I have tried:
- Use Access 2007 to hold a linked table to the list. Can query the list in Access, but SQL Server doesn't seem to be able to go the double jump (to Access and then to Sharepoint). SQL can query a native Access table just fine.
- Use Reporting Services to query the same Access 2007 linked table. Works fine, but the problem is that I am using a separate RS data source and datasets in my report and need to combine the data from sharepoint with data from other data sources. Suggestions for this?
- Set up an RS data source as XML rather than text query. Works ok on my workstation, not on the server itself. But the main problem is again as above, this is a separate data source that needs joining with other data sources.
Many thanks to anyone who can help!
August 1, 2007 at 6:02 pm
Its good to answer your own questions - means you've learned something and now someone else can gain from your hard-made sweat!
My solution was to use SSIS to pull the Sharepoint list into a data warehouse table designated for the purpose. I used an Access linked table as mentioned above, and a data flow task in SSIS to read the data from it into a SQL table. Be sure to have the Microsoft ACE data provider installed if you are using Access 2007 (or any Office 2007 product) as the .accdb file structure is different from the previous .mdb file and the Jet provider cannot read it. The SSIS package can be saved to the server and scheduled as a job to run at x interval to provide refreshed data. My Reporting Services report will now always have the latest user-input data combined with the other data that is needed. If your needs require that you absolutely have to have up-to-the-moment information, then you might end up looking at creating a RS report that uses SSIS as a source. Not my case, so I'll leave that as an exercise for another day.
In my case, we have been building a data warehouse for some time and the idea for using Sharepoint as a way to allow users some control or influence on their reports was an afterthought. It turns out that this solution works perfectly for us because we a collecting our data in a central repository for reporting purposes and the table representing use input already existed. It was simply a matter keeping that table updated with as little intervention as possible. Thank you Sharepoint, SSIS, Access, and RS!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply