Sql-server – SQL Agent job for SSIS execution fails with “An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed.”

sql serversql-server-agentssis

A SQL Agent job with a SQL Server Integration Services Package step fails with the following message:

Executed as user: DOMAIN\SQLAGENTACCOUNT. Microsoft (R) SQL Server Execute Package Utility Version 13.0.4259.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed. The command line parameters are invalid. The step failed.

Best Answer

This error can occur if the Configuration tab on the job step is used to set package parameters and one of the parameters (such as password) contains a character that needs to be delimited, such as a single quote. When the parameters are modified on the job step dialog, the parameters are included on the job step's command line, and a single or double quote will not be delimited. This will result in an invalid (or worse, erroneous) command line.

Possible solutions are:

  • Delete the job step, create a new job step, and set the package parameters on the SSIS project directly (in SSMS, under Integration Services, SSISDB, SSIS Packages, Projects). The command line for the job step will not try to pass the parameters, but the package execution engine will read the parameters directly and do a better job of handling special characters.

  • It may be possible (but I haven't tested it) to include the backslash delimiter before the special character. So for a password of My'Pa$$word you would instead use My\'Pa$$word.

  • Remove the offending special character from the parameter (e.g., if it's a password, generate a new password without any quotation marks in it).

I don't know what all characters can cause this issue, but I do know that single quotes will cause it, and I think it's safe to assume double quotes will also.

You can see what command will be executed by SQL Agent by running the following query:

USE msdb
SELECT t1.command FROM sysjobsteps t1
JOIN sysjobs t2 ON t1.job_id = t2.job_id
WHERE t2.name = '<SQL AGENT JOB NAME>'

The command will include a couple of /Par by default for $ServerOption::LOGGING_LEVEL and $ServerOption::SYNCHRONIZE, but if any parameters on the job step were modified you'll also see them in the command line, such as:

/Par "\"SQLServer_Password\"";"\"PLAIN_TEXT_PASSWORD\""

Note that for password fields, the password gets stored in sysjobsteps in plain text, so that's definitely not something you want to change on the job step.

There may be other issues that could cause invalid command errors, and anyone is welcome to edit this answer to add any others.

Related Question