//
home

Latest Post

Customizing SSRS Subscription Schedules

How frustrating is it when setting up an SSRS report subscription to be stuck with the standard  run times Daily, Hourly etc. With no options for adjusting or customizing the schedule to lets say just running during core business hours or exclude weekends etc. I have always found this to be a major shortcoming of SSRS report subscriptions and extremely frustrating. If you look on the web there are tons of posts where this feature is immutable and can not be altered and\or changed. Well I am here to tell you that there is hope.

As you may or may not not know SSRS creates a scheduled job to process the subscription request and as such it can be modified like any other job. The problem is that the job name is a GUID and if you have many subscriptions running at similar times and intervals,  it is not always intuitive  as to which job relates to which subscription. Thankfully there is an “APP” for that or more accurately  a query.

USE [ReportServer]; 

GO
 
SELECT USR.UserName AS SubscriptionOwner
      ,SUB.ModifiedDate
      ,SUB.[Description]
      ,SUB.EventType
      ,SUB.DeliveryExtension
      ,SUB.LastStatus
      ,SUB.LastRunTime
      ,SCH.NextRunTime
      ,SCH.ScheduleID AS JobName
      ,sch.*
      ,CAT.[Path] AS ReportPath
      ,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
     INNER JOIN dbo.Users AS USR
         ON SUB.OwnerID = USR.UserID
     INNER JOIN dbo.[Catalog] AS CAT
         ON SUB.Report_OID = CAT.ItemID
     INNER JOIN dbo.ReportSchedule AS RS
         ON SUB.Report_OID = RS.ReportID
            AND SUB.SubscriptionID = RS.SubscriptionID
     INNER JOIN dbo.Schedule AS SCH
         ON RS.ScheduleID = SCH.ScheduleID
ORDER BY USR.UserName
        ,CAT.[Path];

Once you identify the job by the jobname(ScheduleId), it is simply a matter of creating a custom job schedule to meet your needs and deleting or disabling the existing schedule. You can combine any number of schedules to cover weekdays, weekends, after hours etc.

 

Bon Appetit