//
you're reading...
BI, SSRS

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

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: