In a place where I work we have lots of subscriptions set for SSRS reports. Some reports have as many as 100 subscriptions set up.
Our data warehouse is refreshed every night, with all the data being dropped and loaded in again.
Because of that, on occasion when things go wrong and the data isn't loaded up in the morning when the subscriptions are supposed to run, they are unable to fill in the parameters and get bugged like this:
Now to fix them, we have to manually open every subscription and hit save again. Sometimes this is not enough and we have to manually set up every parameter again. This is very frustrating and time consuming. So this brings me to my question:
How to prevent this? Is there a way to automatically 'refresh' all subscriptions? Other solution I have thought of, but lack the knowledge of ReportServer db to execute is:
Perhaps run a script before the subscriptions are set to execute. The script would check if data exists in warehouse database tables. If there is no data, delay subscriptions for an hour and then try again. Is this doable? If it is, I would be extremely grateful for a query that gives me access to execution times of subscriptions. I tried and couldn't build one myself.
Any other solutions are extremely welcome, thanks in advance!