I'm working on an application that allows users to set up scheduled time based reports. Each scheduled report creates a SQL Agent job associated with a schedule.The default time to fire these off is 8:00 AM. There are several hundred. They run off a Kimball DWH and it has no trouble running hundreds of reports all fired off at the same time.

There are several ETL processes and occasionally they don't complete before our verbal SLA of 8:00 AM.

My problem is on days where the ETL runs past 8:00 AM I wan't to hold these scheduled jobs from firing off.

I have a klunky solution but was wondering if there are any best practices around this and what people's opinions are on the matter.

Best Regards

Not very clear. :(

Unfortunately, there is no real way to handle what you describe.

What we do is use a unique account for SSRS to connect to the database.  When the ETL is running, we disable the SSRS account, when it is finished, we enable it.    This causes the scheduled reports to fail when the ETL fails, or runs long.   Then you have to rerun the scheduled reports, which failed.  You could create an automation which reruns failed scheduled report Agent jobs, we have not gone that far.

This method protects against someone scheduling a report during your ETL load and getting incomplete or wrong data.

• Proposed as answer by Qiuyun YuMicrosoft contingent staff, Moderator Tuesday, June 23, 2015 1:16 AM
• Marked as answer by Qiuyun YuMicrosoft contingent staff, Moderator 22 hours 43 minutes ago

Thanks for the tips.

What I ended up doing is hijacking the AddEvent stored procedure call.

If the ETL hasn't completed, this sproc directs all subscription events to a new table I created. When the ETL finishes I have a process that trickles the data back into the Event table.

Works very seemlessly.

• Marked as answer by James Braum 8 hours 38 minutes ago
• Edited by James Braum 8 hours 37 minutes ago