SQL Server Reporting Services (SSRS) - Options for Dynamically Setting Report Data Source

I received a call from one of my colleagues in the Oakton Canberra office today who was working at a Federal Government department. He was using a SharePoint-integrated mode of SSRS.
He wanted deployed reports to point to the correct datasource without manually updating the DataSource of the report each time a new site is provisioned. It was not a feasible option for him to be manually changing the report datasources each time a new site in SharePoint was deployed (based on a particular site template that included Reports) - especially when he moved back off site.

I suggested a couple of ways forward - each with their own advantages and disadvantages:
1) Create a custom SQL Reporting Services Reports Extension that can source data dynamically (as outlined here http://msdn.microsoft.com/en-us/library/microsoft.reportingservices.dataprocessing.aspx and here http://msdn.microsoft.com/en-us/library/bb283184.aspx)
2) Using Expression Based Connection Strings
3) Setting the datasource at Deploy Time instead via Powershell or another scripting technology (or as part of a feature event receiver in SharePoint)

The Deploy-time option was the simplest and cleanest option (though it would have a limitation of breaking if the site locations are moved). A script would need to update the datasources as part of the site migration process. This was deemed to be an acceptable tradeoff.
DDK

Fix - SQL Reporting Services 2008 - An error occurred during local report processing. Access Denied with HRESULT 0x80030005

While attempting to preview a report developed by another user (after getting it out of Visual Source Safe), I was confronted by the following error:

An error occurred during local report processing. Access Denied. (Exception from HRESULT: 0x80030005 (STG_E_ACCESSDENIED))

I created another report exactly the same and it seemed to work fine - so there was some other kind of caching issue causing this error.

After investigating, I noticed that the REPORTNAME.rdl.data file (which is a binary file and is how SSRS reports cache their preview information) - was read-only. SSRS was trying to write to the file and consequently getting the access denied errors. This was because the rdl.data file had erroneously been put into source control.

The fix was to remove it from source control (as it should be generated as each user previews the report), and mark the local file as read only.

Of course it would be much nicer (and simpler to troubleshoot the issue) if this particular "Access Denied" error message was more descriptive and actually told me *which* file it was trying to access.

The remote name could not be resolved (Microsoft.SqlServer.Management.UI.RSClient)

As per Pete Wards blog (http://wardyit.com/blog/blog/archive/2006/08/15/139.aspx), this error may be caused as a result of modifying the machine name.
In order to resolve the error you need to ensure that the element http://localhost/reportserver in the rsreportserver.config is configured to the correct computer name.
By default the rsreportserver.config file is in the directory C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\.

David Klein www.ssw.com.au