enum failing when used as a type in param()

I’ve a Function that I use an enum to help check values passed to one of the parameters.

enum allowedsubsystem
{
activescripting
analysiscommand
analysisquery
cmdexec
distribution
logreader
merge
powershell
queuereader
snapshot
ssis
transactsql
}

I’ve declared the parameter in the function like this:

[Parameter(Mandatory=$False)]
[allowedsubsystem]$SubSystem

The function is called like this:

$Params = @{
SqlServer = 'D01\DEV'
RetryAttempts = '1'
OnSuccessAction = 'GoToNextStep'
JobStepName = 'devtesttest - Exec Daily Master DW ETL - IsDailyLoad False'
OnFailAction = 'GoToNextStep'
Command = '/ISSERVER "\"\SSISDB\DW\Data Warehouse ETL\DW Master ETL.dtsx\"" /SERVER "\"D01\DEV\"" /Par "\"$Project::DWDatabaseConnectionString\"";"\"Data Source=SQLD01\DEV01;Initial Catalog=reporting_devaca1;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$Project::IsDailyLoad(Boolean)\"";False /Par "\"$Project::IsStandardEdition(Boolean)\"";False /Par "\"$Project::SourceDatabaseConnectionString\"";"\"Data Source=SQLVD01\DEV01;Initial Catalog=devtesttest;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";2 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
SubSystem = 'ssis'
Databasename = 'ETLD01\DEV'
JobName = 'DW - SSIS_Daily Master DW ETL - Run All In batches - STEP 2 - Cornerstone - D01 - 1'
RetryInterval = '10'
}
Add-SqlAgentJobStep @Params

But when I run it, I get this error, see below, if I remove the enum and set the parameter to string, it works ok.

What am I doing wrong?

Exception calling "Create" with "0" argument(s): "Create failed for JobStep 'devtesttest -
Exec Daily Master DW ETL - IsDailyLoad False'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for JobStep
'csdevtesttest - Exec Daily Master DW ETL - IsDailyLoad False'. ---> Microsoft.SqlServer.Management.Smo.PropertyNotSetException: To accomplish this
action, set property Server.
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValue(String propName)
at Microsoft.SqlServer.Management.Smo.Agent.JobStep.GetAllParams(StringBuilder sb, ScriptingPreferences sp, Int32& count)
at Microsoft.SqlServer.Management.Smo.Agent.JobStep.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean
skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
at CallSite.Target(Closure , CallSite , Object )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
TargetObject :
CategoryInfo : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
ErrorDetails :
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at Add-SqlAgentJobStep<Process>, <No file>: line 181
at <ScriptBlock>, <No file>: line 13
PipelineIterationInfo : {}
PSMessageDetails :

 

I am suspecting a type related issue. Can you try converting $subsystem to string as the passed value will be type casted to allowedsubsystem type the function which could be something not supported by the cmdlet that you are using. So you can try converting it to string.

$SubSystemString = $SubSystem.ToString()

And then use $SubSystemString while calling the actual cmdlet.

I did this, see below, but still failed…same error.

[string]$SubSystem = 'ssis'
Add-SqlAgentJobStep @Params -SubSystem $SubSystem

Command = '/ISSERVER "\"\SSISDB\DW\Data Warehouse ETL\DW Master ETL.dtsx\"" /SERVER "\"D01\DEV\"" /Par "\"$Project::DWDatabaseConnectionString\"";"\"Data Source=SQLD01\DEV01;Initial Catalog=reporting_devaca1;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$Project::IsDailyLoad(Boolean)\"";False /Par "\"$Project::IsStandardEdition(Boolean)\"";False /Par "\"$Project::SourceDatabaseConnectionString\"";"\"Data Source=SQLVD01\DEV01;Initial Catalog=devtesttest;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";2 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'

Try running the command directly and see the results.

Also, try using the ValidateSet or ArgumentCompleter attributes instead is enum.

Thank you.


The command works ok, I’ve shown that by removing the enum from the paramter declaration.

This works

[string]$SubSystem

This fails


[allowedsubsystem]$SubSystem

The enum used is:

enum allowedsubsystem {
    activescripting	
    analysiscommand	
    analysisquery
    cmdexec	
    distribution
    logreader
    merge	
    powershell
    queuereader	
    snapshot	
    ssis
    transactsql
}

I have the parameters declared like this:

[CmdletBinding()]
    param (
    [parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$SqlServer ,
    [parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$JobName ,
    [parameter(Mandatory=$true)]
    [ValidateNotNullOrEmpty()]
    [string]$JobStepName,
    [Parameter(Mandatory=$False)]
    [int]$StepID ,
    [Parameter(Mandatory=$False)]
    [string]$Command ,
    [Parameter(Mandatory=$False)]
    [string]$DatabaseName ,
    [Parameter(Mandatory=$False)]
    [allowedsubsystem]$SubSystem , 
    [Parameter(Mandatory=$False)]
    [string]$OnSuccessAction ,
    [Parameter(Mandatory=$False)]
    [string]$OnFailAction ,
    [Parameter(Mandatory=$False)]
    [string]$RetryAttempts ,
    [Parameter(Mandatory=$False)]
    [string]$RetryInterval 
    )

@ Kiran,

I’ve not used ValidateSet in the parameter declaration, as i don’t want to have to put all these options in every Function. That, if I understand correctly, is what enum is for, so I can declare these options once, and there after refer to it in functions as required.

The option of using ArgumentCompleter is not an option in this case, if I understand correctly what it’s for and how it works.

You can also use the ValidateSet attribute like this as well…

[pre]class SupportedCountries : System.Management.Automation.IValidateSetValuesGenerator
{
[string] GetValidValues()
{

Write your code here

$Countries = @(‘India’, ‘USA’, ‘UK’, ‘Canada’, ‘Australia’)
return $Countries
}
}

Function Get-Country
{
[CmdLetBinding()]
param
(
[parameter(Mandatory = $true)]
[ValidateSet([SupportedCountries])]
[string] $CountryName
)

Write your code here

Write-Host $CountryName
}[/pre]

Whit I meant is to receive the input as enum then convert it to string before passing to actual cmdlet.

Can you post the line 181 as mentioned by the stacktrace

ScriptStackTrace : at Add-SqlAgentJobStep<Process>, <No file>: line 181

@ Kiran,

Apparently ‘System.Management.Automation.IValidateSetValuesGenerator’ is only available in PS 6+

https://docs.microsoft.com/en-us/dotnet/api/system.management.automation.ivalidatesetvaluesgenerator?view=pscore-6.0.0

@ Kvprasoon

Line 181 in the try in the try … catch that holds the create

try { # <---line 181
$NewStep.Create()
} catch {
$_.Exception.Message
$_.Exception.ItemName
Break
}

This is the process step of the function

$SQLSvr = New-Object Microsoft.SQLServer.Management.Smo.Server $SqlServer
$JobServer = $SQLSvr.JobServer
$Job = $JobServer.Jobs[$JobName]

$NewStep = New-Object Microsoft.SqlServer.Management.SMO.Agent.JobStep
$NewStep.Name = $JobStepName
$NewStep.Parent = $Job
$NewStep.Command = $Command
$NewStep.SubSystem = $SubSystem
if($SubSystem -eq 'ssis'){$NewStep.DatabaseName = $DatabaseName}
$NewStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnSuccessAction
$NewStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnFailAction
$NewStep.RetryAttempts = $RetryAttempts
$NewStep.RetryInterval = $RetryInterval
if($StepID){$NewStep.ID = $StepID}
#$SQLJobStep.DatabaseName = $SsisServer

try {
$NewStep.Create()
} catch {
$_.Exception.Message
$_.Exception.ItemName
Break
}

 

Thanks for sharing the snippet, so what I was trying to convey is to do the below.

$NewStep.SubSystem = $SubSystem.ToString()

Yes, got that, and tried it, and it failed with the same error.

@ kvprasoon,

Not sure what happened the first time I attempted that but I’ve retired it multiple times now and each time it’s worked and created the step.

Nice to get it to working, but it’d be great for me to understand why this was an issue…? The parameter was accepted fine without the enum being used.

$NewStep = New-Object Microsoft.SqlServer.Management.SMO.Agent.JobStep
$NewStep.Name = $JobStepName
$NewStep.Parent = $Job
$NewStep.Command = $Command
$NewStep.SubSystem = $SubSystem.ToString()
if($SubSystem -eq 'ssis'){$NewStep.DatabaseName = $DatabaseName}
$NewStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnSuccessAction
$NewStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnFailAction
$NewStep.RetryAttempts = $RetryAttempts
$NewStep.RetryInterval = $RetryInterval
if($StepID){$NewStep.ID = $StepID}

try {
$NewStep.Create()
} catch {
$_.Exception.Message
$_.Exception.ItemName
Break
}

Form what I understood, $NewStep.SubSystem requires the type to be specific and can only type cast from a string type, so custom enum type cannot be converted to the expected Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem type.

Thanks!

I appreciate the help and the info.