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 asubOn 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.