In order for SQL Server Reporting Services to deliver a report to the destination of your choosing, you only need to create an assembly with class types implementing 3 simple interfaces: IExtension, IDeliveryExtension, and ISubscriptionBaseUIUserControl.
Then the fun really begins. I have some tips to share for anyone else who tries. Refer to the last post for the source code.
My delivery extension needs to plug into both the user interface of the Report Manager (for the user to set delivery parameters), and into the ReportServerService (where all the heavy lifting and rendering takes place). After every build I had to get the assembly into the bin directory of both the Report Manager (the web application), and the ReportServerService. All this takes is a little batch file (like the following) and a command prompt with admin privileges.
net stop reportserver xcopy /y (BuildPath) (SSRSHome)\ReportServer\bin xcopy /y (BuildPath) (SSRSHome)\ReportManager\bin" net start reportserver
There is no need to shutdown the Report Manager web application. ASP.NET shadow copies the extension assembly and leaves the original unlocked in the bin directory. The runtime will recognize when the assembly has changed and loads the new version immediately.
The next step was to have SSRS accept my new delivery extension with loving arms. There were 4 total configuration files to modify. The RSWebApplication.config and RSReportServer.config files are easy to figure out. Just provide SSRS with the type name and the assembly name at the appropriate location in the XML:
<Extensions> <Delivery> <Extension Name="Report Server Blog" Type="OdeToCode.BlogDeliveryExtension.BlogDeliveryProvider, BlogDeliveryExtension"> <MaxRetries>3</MaxRetries> <SecondsBeforeRetry>900</SecondsBeforeRetry> </Extension> </Delivery> </Extensions>
The rssrvpolicy.config and rsmgrpolicy.config policy files are a different story. These files manage the security policies of SSRS. After nearly blacking out from reading the documentation on code groups for the 15th time and not having any success, I found Bryan Keller’s post with a hint on where to place extension code groups (right after the CodeGen membership group).
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Report Server Blog" Description="Code group for OdeToCode Blog Extension"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\Bin\BlogDeliveryExtension.dll" /> </CodeGroup>
Typos, or incorrect element placement can lead to a wide variety of interesting exception messages and log file entries. Any exception with “security”, “permission”, or “cannot load type” in the description is a possible user malfunction in editing the code groups.
To debug the UI behavior I’d attach to the ASP.NET worker process. To debug the actual delivery, I’d attach to the ReportServerService, or use System.Diagnostics.Debugger.Launch() to bring up a debugger as soon as it hit the line of code.
To execute the delivery I first needed to setup a subscription to a report. I setup a subscription to run a report every Monday morning at 8 am. I’ve got the debugger ready and just need to wait a few days now for the breakpoint to hit.
There is a SQL Agent job for each schedule in SSRS. By executing the Agent job, you can trigger the subscription to fire off a delivery. Finding the right job can be problematic if you have several report subscriptions set, but scanning the results of the following query might help.
SELECT RS.ScheduleID, C.Name, U.UserName, S.Description FROM ReportSchedule RS INNER JOIN Subscriptions S ON RS.SubscriptionID = S.SubscriptionID INNER JOIN Users U ON S.OwnerID = U.UserID INNER JOIN [Catalog] C ON RS.ReportID = C.ItemID
|B20C9057-EE51-41E2-B3B5-7450AC73FFCB||Customers Report||REPORTING\bitmask||Post report to http://ibm600xp/dottext/scott/services/simpleblogservice.asmx|
That concludes the tips for now. Happy 100th post to me.