I have some complex data-driven subscriptions in SSRS that both attach a report to the email and places a summary of the data within the email body template.

Sometimes an upstream ETL process fails and an SSRS report gets sent out that has invalid data. After the ETL issue is fixed, I need to send out a correct report to the stakeholders. I can either:

1. Reschedule the report to a time a few minutes in the future, wait for it to send, and go back and correct it back to the original schedule,
2. Run it manually within SSRS web interface/Visual Studio/Report builder and send an email out from my account,
3. or …use SSMSTools SQL Snippets and open a query window, type “reports” and hit [tab] type in the name of the report, and have it spit out an EXEC statement that will retrigger the schedule for me immediately.

Here is a SQL Snippet I use frequently to locate SSRS Report subscriptions in case I need to run it manually.

SELECT
'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''
+ CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
, b.name AS JobName
, a.SubscriptionID
, e.name
, e.path
, d.description
, laststatus
, eventtype
, LastRunTime
, date_created
, date_modified
FROM
ReportServer.dbo.ReportSchedule a
INNER JOIN msdb.dbo.sysjobs b ON convert(varchar(max),a.ScheduleID) = b.name
INNER JOIN ReportServer.dbo.ReportSchedule c ON b.name = convert(varchar(max),c.ScheduleID)
INNER JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
INNER JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE e.Name like '%{C}%'

Later, I plan on alleviating most of the ETL upstream incomplete issues with my ETL process dependency application, but this will do for now.

How do you handle upstream ETL processes failing, and the downstream reports?