automatically manage a failed ssrs subscription

Question

So, I want to automatically re-fire failed ssrs subscriptions (most of them are data-driven subscriptions) : during the weekend (mainly saturday) we have many data-driven subscriptions that run and almost every weekend we have failures, so, monday, we have to manually identify the failed parameter values and then re-run the failed subscriptions on the parameter values they failed on.

Generally the parameters are cities and postal codes.

So, i think we can divide the problem into 2 sub-problems :

1- how to track / log the parameter values for which the subscription failed ? actually we are doing it manually by doing a minus between all the values - generated files.

2- is it possible to create a "dynamic subscrption" that fires once the first is finished and that targets only the "failed parameter values"

i hope i explained myself well

thank you in advance

Kind regards

I don't think this is going to be straight forward. I don't think there is a record of what parameters were used for any given Data Driven subscription. The dataset generated for the subscription could be 1 record or a 1000 records. so how would you know what parameter was used if there was a single failed subscription out of the 1000 for the same report on the same run? Or does the whole subscription fail regardless of how many the subscription dataset returns? — Harry
thank you for your reply Harry, that's what i'm looking for, i thought maybe ssrs can log in things like "report A failed when launched with parameter X.." — Med-2022
You will have to write something like that. Put the dataset that the subscription uses into a table with an additional column to record success or failure. Then for each record, run the subscription and log the outcome of that by updating the success fail column.. then proceed to the next one. once there are no more rows to be processed, get something else to write all failed rows to a staging table for you to review and resend subscription for those rows by following the same steps above. — Harry

Soundappan A Soundappan A · Accepted Answer · 2020-05-19T10:52:37

Below query is useful to find out the subscription failed reports. You should have the access of ReportServer DB.

select c.ItemID as ReportId,
       c.Name,
       c.Path,
       s.SubscriptionID,
       s.LastStatus,
       s.LastRunTime,
       s.DeliveryExtension
from ReportServer.dbo.Subscriptions s
inner join ReportServer.dbo.Catalog c
on s.Report_OID=c.ItemID 
where s.LastStatus like '%Fail%'

Column [LastStatus] contain subscription execution status.

automatically manage a failed ssrs subscription

1 Answers