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.
