The Blog Delivery Extension

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.

Building The Delivery Extension

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.

Configuring The Delivery Extension

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.

Debug The Extension

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.

Just kidding.

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
 
ScheduleID
Name
UserName
Description
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.