Here’s another case where “rolling your own” can really help…
There are one or two things about SSRS subscriptions (data-driven or not) that really bug me:
- I can’t stand going through that wizard, especially when my target is a fileshare, which means I have to supply credentials (and look them up again) on a step I have NO INTENTION OF CHANGING, just so I can change one parameter on a later step, or set a new ad-hoc rundate on a later step.
- I can’t always use the simplistic schedules the wizard allows you to set up — which often mean I am reduced to ad-hoc rundates quite often.
Oh — I also can’t stand the GUIDiotic names each SSRS Wizard-produced Agent job ends up having. I know why they do it, and it’s forgiveable, but it tends to clog up my view of Agent jobs, considering that the ReportServer database is typically not the only one on the server instance.
Here’s a real-life example, which will show you how I deal with both pet peeves and give you some help, I hope, with similar scenarios in your environment.
Case in point
I have an event that must be invoked “On Friday, when the subsequent Saturday is the first Saturday of the next month.” Please note that this is not the same as “the last Friday of the month”, because if Friday’s date is (say) the 27th of the month, the subsequent Saturday is not the right Saturday.
This is not something you can do easily using the aforementioned SSRS wizard’s schedule step, but it’s fairly trivial to figure out with SQL. Code such as the following is easily put in a SQL task in SSIS, or in a TSQL step in a SQL Server Agent job:
declare @Today as date = getdate();
declare @Tomorrow as date = DateAdd(day,1,@Today);
declare @IsFriday as bit =
case when datename(weekday,@Today )=‘Friday’ then 1 else 0 end;
— adjust the above depending on @@LANGUAGE in your environment
— or do it however you prefer!
if @IsFriday = 1
begin
— is tomorrow the first Saturday of the next month?
if day(@Tomorrow)<= 7
begin
exec— … what goes here ?
end
end
… but how do you figure out what to exec?
The answer, my friends, is blowing in the wind
[sorry about the slight misquote, but Bob Dylan fans should note that a TRULY NEW album by Our Guy is coming out this month – happy 50th anniversary!]
First, set up a subscription the way you think you want it, with whatever schedule you like, using the wizard. Typically this will be a “run once” subscription, and you’ll use it to test all your other settings for the subscription, which can be quite complex if you’re data driving and/or have a lot of parameters for this report.
The next thing to do is to figure out which GUIDdily named Agent job stands behind the subscription you just created. What you know so far is the descriptive name you just gave the subscription, so you can find out the subscription ID and the related internal schedule ID, and confirm that you’re picking up the correct one by checking a couple of other details, using a statement such as this in your ReportServer db context:
select ss.SubscriptionID, ss.ModifiedDate, cc.Name, rs.ScheduleID
from Subscriptions ss
join Catalog cc on ss.Report_OID = cc.ItemID
join ReportSchedule rs on ss.SubscriptionID = rs.SubscriptionID
where ss.Description = ‘Your Subscription Description’
… At this point, you may have everything you need if (say) what you want to do is dynamically adjust the parameters in the subscription without messing about with the Wizard; you can go programmatically to the Subscriptions table and adjust the xml-based contents of the ExtensionsSettings, Parameters, and (if the subscription is data-driven) DataSettings columns.
But maybe you want to do more.
Suppose you want to invoke the subscription ad-hoc, again without having to go in and goose the schedule using the wizard; wouldn’t it be nice to simply have a clone of the Agent job, with a non-GUIDdy name, so you could just right-click on the Agent job and run it any time you want?
Or suppose that, as in my example, your scheduling needs are complex, and you’d like to surround the invocation of the subscription with some additional/conditional logic?
Or suppose — as also happens to me — it’s important that two or more subscriptions be invoked in a precise sequence; wouldn’t it be nice to combine the commands for each subscription into steps in one SQL Agent job, or one SSIS workflow?
With the information you in hand from your last query, you can grub up the information you need about your Agent job, like this:
select step_name, sj.job_id, j.name
from msdb.dbo.sysjobsteps sj
join msdb.dbo.sysjobs j on sj.job_id = j.job_id
where step_name like ‘%418FD967-BB9A-49B6-9F35-F428F76B5E39%’
— substitute the ScheduleID you got in the last query, in the
— literal comparison value you use
The j.name value in the query above will give you another GUIDerrific value, and this is the one you’ll see in the SQL Agent list naming the job.
Notice that we’re comparing to a step name, not a job name, or a job ID, when we request this information from msdb’s information about system jobs, even though we are using something called a “Schedule ID” within the ReportServer database. The job name may actually be the same GUID you got as a “Schedule ID”; in some cases I’ve noticed it is, in others it isn’t. The difference may have to do with how much editing you’ve done of this particular subscription or its schedule. Using the step name seems to work for me.
And notice, also, that we’re using a like comparison (don’t forget the %s surrounding the GUID value!) in the where clause above. The step name is likely to be something similar to 418FD967-BB9A-49B6-9F35-F428F76B5E39_step_1, in the case of the ScheduleID value I’ve used in my example, not the GUID alone.
Once you’ve got the job in hand, you’re in like Flint. You can script out the job, recreate it with a name of your own, sensible, choosing, so that you can invoke it at will. You can examine the step’s exec statement, and surround it with whatever additional logic you need, or sequence multiple steps, from multiple jobs, however best suits your purposes.
And — most importantly — you can avoid that pesky Wizard when you’re trying to get real work done.
Have fun!