Data-Driven Subscriptions in SQL Server

I recently had a project that used data-driven subscriptions for bulk processing of SSRS reports. Subscriptions is a very powerful tool, but it lacks centralized monitoring tools, so I had to dig deep into the content database where SSRS service maintains all processing data. By default, this database is called ReportServer.

List of reports published to SSRS service is stored in Catalog table where each report is assigned an ItemID. Report can be associated with multiple subscriptions. Information about each individual subscription is stored in Subscriptions table. Here is a query that retrieves a list of reports with their corresponding subscriptions:

Select

       c.ItemID,

       c.Name,

       s.*

From

       [dbo].[Catalog] c

       Inner Join [dbo].[Subscriptions] s On s.Report_OID = c.ItemID

Subscription has many parameters defined by the user when subscription is created and stored in various text columns of Subscription table in the form of XML.

For example, ExtensionsSettings column stores information about rendering format, destination and file name settings, etc. It can be accessed by converting XML data into a record set and running XML query functions on it such as:

;WITH x AS (Select SubscriptionID, CAST(ExtensionSettings as xml) as ExtensionSettingsXML From [dbo].[Subscriptions])

Select

       s.SubscriptionID

       ,ExtensionSettingsXML

       ,ExtensionSettingsXML.v.value ('Name[1]', 'varchar(100)') as ParamValue

       ,ExtensionSettingsXML.v.value ('Field[1]', 'varchar(100)') as ParamValue

       ,ExtensionSettingsXML.v.value ('Value[1]', 'varchar(100)') as ParamValue

From

       [dbo].[Subscriptions] s

       Inner Join x On x.SubscriptionID = s.SubscriptionID

       CROSS APPLY ExtensionSettingsXML.nodes ('/ParameterValues/ParameterValue') as ExtensionSettingsXML(v)

When Subscription is initiated, it creates a record in ActiveSubscriptions table for each executing instance. Once processed, this record is removed.

Select * From [dbo].[ActiveSubscriptions]

Data-Driven Subscription works by generating a dataset (by executing a query) that provides values for report input, mapping those values to report parameters and queuing report execution. You can also use input data set attributes to specify file name for report rendering, control location where files are saved, and other parameters.

Table Events serves a role of execution queue where a record is created for each actual instance of the report with set parameters. For example, if your input data set contains 5000 records, Events table will have 5000 records one for each instance of report execution. Records are deleted from Events table as soon as report execution is complete.

Select * From [dbo].[Event]

With all that information in mind, you can create a SSRS report that queries ReportServer database and reports status of all active subscriptions. For example, you can use the following query to get this information:

Select

       s.SubscriptionID,

       'ReportName' = c.Name,

       'ReportPath' = c.Path,

       'SubscriptionDesc' = s.Description,

       'SubscriptionOwner' = us.UserName,

       'LatestStatus' = s.LastStatus,

       'LastRun' = s.LastRunTime,

       asub.ActiveID,

       asub.TotalNotifications,

       asub.TotalSuccesses,

       asub.TotalFailures,

       n.RemaininReports

From

       Subscriptions s

       join Catalog c on c.ItemID = s.Report_OID

       join ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

       join Users uc on uc.UserID = c.ModifiedByID

       join Users us on us.UserID = s.OwnerId

       Left Join ActiveSubscriptions asub On asub.SubscriptionID = s.SubscriptionID

       Left Join

              (

                     Select

                           SubscriptionID,

                           ActivationID,

                           count(*) as RemaininReports

                     From

                           Notifications n

                     Group by

                           SubscriptionID, ActivationID

              ) n On asub.ActiveID = n.ActivationID

Base on this users will be able to monitor existing subscriptions and see their last status as well as monitor active subscriptions and their progress.