Aloha!

(If you want to cut to the chase and just get the generic PowerShell that will find all possible combinations, this gist here is what you’re looking for. If however you want a bit of context then read on…)

So, I’ve recently been working on a solution for automating SQL Agent Job deployments[1], and I came across a problem. In my solution, the constituent parts of a SQL Agent Job would be stored in an XML file. So job name, schedules, steps etc would be organised by elements. And I wanted people to be able to pass the days that the job would run as “Monday”, “Tuesday” etc. However, days that a job are run on is not stored literally as days of the week, is stored as an enum integer in the database. It’s simple enough to write a script to sum up all these to pass a value when assigning the Frequency Interval. So this is all very nice; the XML structure can include intervals of actual days instead of enums. Code for this function is below.

This was not the problem: the real problem is that I wanted to write a PowerShell script that would enable users to extract a SQL Agent Job that already exists into the XML structure required. This would be a huge time saver where there are a large number of Jobs to extract from an instance into source control. And so I needed a way in which to take the sum of the days and figure out the combination, then convert it into the literal days of the week.

What makes this challenging, and in fact what was off-putting about this whole endevour, is that 65 can have multiple combinations, not all of which are correct. 65 can mean one of three things:

Returning multiple values via a Function in PowerShell is quite straightforward; we’re going to make use of a custom object that will be passed to an array. We can then whatever we need to do with the array outside of the function. In this case, by recursively looping through all possible combinations we can find the right combination, we can return the combinations and then transform back into days of the week.

The functions to return the list are below.

So in the case of needing to find 42, AKA Monday, Wednesday and Friday, the result would be 2,8 and 32, and this would be the only combination returned.

And so the final part would be converting the list into the relative days of the week. Again this is not problematic and is a reversal of the first function:

Neat.

That’s all for this post. Mahalo!

[1] I don’t really want to go into the “why”, or even too much of the “how” I am using xml and PowerShell to automate the deployment of SQL Agent Jobs in the main part of this post because I just want to focus on the summing of values. In fact I go into quite a bit more detail than I originally intended, but some context is required. But for those of you that want to know even more about what I am trying to achieve here, this rather verbose footnote may help explain…

Essentially for anyone who has ever written a SQL Agent Job, or managed a database instance that has SQL Agent Jobs on it will know that there’s no really easy way to have them redeployable. For those of you thinking that you can just script the job out, this is a fair point.  But in reality this doesn’t really work for any job that isn’t too sophisticated: If you have dtsx packages that are stored in SSISDB and they use environment references, chances are the environment reference is going to be different on another instance. I mean let’s not get into the whole “does the object that a step is trying to execute exist or not” element of SQL Agent Jobs.

There’s also the alternative of backing up msdb and restoring that to a new instance. This only works if you want to copy EVERYTHING over, which is often not the case. And permissions for operators are stored in master. So you’ll need both master and msdb. OK, chances are you are backing up both these, but it’s not ideal to have to restore them over another instances databases just to get the SQL Agent Jobs.

So one way I have thought about how to do this is to have the structure of the SQL Agent Job in an XML format, and use SMO to create the job/schedules/operators/steps etc on the instance. And the PowerShell has to work in such a way that it is idempotent: that is, the XML/PowerShell can be executed again and not have any side effects. The caveat to this is that we have to consider the XML to be the desired state of the Job. If indeed the XML does change, then the Job is updated to reflect that change.

Fundamentally SQL Agent Jobs are linked to some functionality somewhere else on the instance: it could be executing dtsx packages, stored procedures… whatever. But they should be tied in with however you are deploying the artefacts underneath. And if you’re not deploying databases or ispacs from source control then you should start there and work your way up to SQL Agent Jobs.