0

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

Med-2022
  • 1
  • 1
  • 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 May 20 '20 at 04:46
  • 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 May 21 '20 at 12:18
  • 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 May 21 '20 at 22:09

2 Answers2

0

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.

Soundappan A
  • 321
  • 3
  • 8
  • thank you for your reply but it does not address the issue here, i have a report base on a query very similar to the one you posted which helps me find the failed reports, the problem is to identify (quickly) what are the parameter values that failed in order to re-run the subscription on these failes parameters (remember that we are talking about data-driven subscriptions), a second step is to automate all this :) again thank you – Med-2022 May 19 '20 at 12:09
0

SSRS subscriptions are jobs in the end, so you can find your job and modify retries attemps, or create dependencies in SSIS or another scheduler. Here is a query to detect which job for which subscription:

SELECT J.JOB_ID,J.NAME
FROM REPORTSERVER.DBO.SUBSCRIPTIONS S
JOIN REPORTSCHEDULE R ON R.SUBSCRIPTIONID = S.SUBSCRIPTIONID
JOIN MSDB.DBO.SYSJOBS J ON J.NAME = CONVERT(SYSNAME, R.SCHEDULEID)
Dordi
  • 778
  • 1
  • 5
  • 14