DBATools - pass through flat file configs to Agent Job Commands

Question

I'm experimenting with source controlling my SQL Agent Jobs & Schedules as .json files and deploying them with the dbatools SQL Agent command suite.

Given $sa = Get-Credential and foo.config of the below form...

{
    "Schedule":                  "Foo",
    "Disabled":                  false,
    "FrequencyType":             "Weekly",
    "FrequencyInterval":         "EveryDay",
    "FrequencySubdayType":       "Time",
    "FrequencySubdayInterval":   0,
    "FrequencyRelativeInterval": "Unused",
    "FrequencyRecurrenceFactor": 1,
    "StartDate":                 "20180823",
    "EndDate":                   "20181023",
    "StartTime":                 "070000",
    "EndTime":                   "235959"
}

Attempting to use foo.config in a solution fails basic parsing with the following message:

~> $foo = Get-Content foo.config | ConvertFrom-Json
~> New-DbaAgentSchedule @foo -ServerInstance "." -SqlCredential $sa

WARNING: [15:50:04][New-DbaAgentSchedule] A schedule was not provided! Please provide a schedule name.

Well that sucks... Especially since the following works just fine...

$bar = @{
    Schedule=                  "Foo"
    Disabled=                  $false
    FrequencyType=             "Weekly"
    FrequencyInterval=         "EveryDay"
    FrequencySubdayType=       "Time"
    FrequencySubdayInterval=   0
    FrequencyRelativeInterval= "Unused"
    FrequencyRecurrenceFactor= 1
    StartDate=                 "20180823"
    EndDate=                   "20181023"
    StartTime=                 "070000"
    EndTime=                   "235959"
}

New-DbaAgentSchedule @bar -ServerInstance "." -SqlCredential $sa

I really don't want to bother typing out each individual ... -Param1 $foo.Param1 -Param2 $foo.Param2 ... because I'm lazy. I would really much rather splat my config files into the various commands. Why won't this work!!?!1!

score 2 · Answer 1 · answered Oct 10 '18 at 15:25

Well, 4 hours ago self... if you had bothered to inspect the types of $foo and $bar, you might not have wasted a whole afternoon swearing loudly in your open office floor plan as you scroll fruitlessly through the documentation...

~> ($foo | Get-Member).TypeName[0]
System.Management.Automation.PSCustomObject

~> ($bar | Get-Member).TypeName[0]
System.Collections.Hashtable

There's even a convenient, generalized solution on this very Q&A Network for converting a PSCustomObject into a HashTable.

$foo.psobject.properties | foreach -begin {
    $foo=@{}
} -process {
    $foo."$($_.Name)" = $_.Value
} -end {$foo}

~> New-DbaAgentSchedule @foo -ServerInstance "." -SqlCredential $sa

Easy peasy, lemon squeezy.

So splat away, ya big crazy. And when you're done feeling stupid, maybe share the workaround online in case it helps someone else. And if you're feeling super proactive, maybe consider setting aside some time to contribute to the module.

score 2 · Accepted Answer · 2018-10-11T19:47:26.597

One thing you can do is there is an option when you convert from json to actually convert it into a hastable for you...so it can save a few lines of code.

(Get-Content C:\temp\foo.config | ConvertFrom-Json).GetType()
<#
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    PSCustomObject                           System.Object
#>
(Get-Content C:\temp\foo.config | ConvertFrom-Json -AsHashtable).GetType()
<#
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Hashtable                                System.Object
#>

I don't have any server accessible to confirm, but the output of the second command though shows it is the name/value format:

Name                           Value
----                           -----
EndTime                        235959
StartDate                      20180823
FrequencySubdayInterval        0
FrequencyType                  Weekly
Schedule                       Foo
Disabled                       False
FrequencyRelativeInterval      Unused
EndDate                        20181023
FrequencySubdayType            Time
FrequencyInterval              EveryDay
StartTime                      070000
FrequencyRecurrenceFactor      1

Being that the above can only work on PowerShell Core 6.1 (generally available now) you will need to use the WindowsCompatibility module to utilize dbatools within PS Core. You can do that using the following code within PS Core:

Install-Module WindowsCompatibility
Import-WinModule dbatools

The second command utilizes WinRm in order to support implicit remoting. The above would mean you have installed PowerShell Core on a Windows Machine. The documentation shows how to do this for a remote machine if required.

From there you can run the dbatools commands as you want:

DBATools - pass through flat file configs to Agent Job Commands

2 Answers2