'Empty string results in "Errors were detected in the command line arguments, please make sure all arguments are set correctly"

On a SQL 2016 Server I have a job that calls an SSIS package. That package is in a project in the SSISDB and has parameters. One of those parameters is a string type that is blank as a default.

I ran the job with the blank value for this parameter, and it ran successfully.

I then opened the job properties, went to the step that calls that package and went into the configuration and gave that parameter a value.

I ran the job again and it ran successfully, and the parameter value had the expected effect on the results.

Now I want to modify the job and set the parameter back to a blank string. Repeating what I did above, I open the configuration and completely delete the value of the parameter.

enter image description here

When I then try to save the Job Step properties, I get an error dialog with the message in my subject line.

If I put a non-empty value back in for that parameter, the job step can be saved.

When I first created the job, I was able to save it with an empty string for that value. It's just when I change the value to a non-empty string, run the job, and then try to change it back to empty, that I encounter this error.

I'm aware that I can run an alter job script or delete and re-create the job step to work around this. I'm not interested in knowing how to work around this problem.

My question is: Is this a known bug, or is there a trick to using the GUI to change the value of a parameter for execution from a string value to an empty string?

Can any of you do it, or is my issue reproducible? I've found nothing in my searches so far.

EDIT: following Aaron's comment, I tried entering a pair of single-quotes for the value of the parameter. The job accepted it and saved successfully. But then when I ran the job, the pair of single quotes were used literally as the value of the parameter, instead of an empty string.

sql-serverssisetlsql-server-2016sql-agent-job


Solution 1:[1]

These problem is posted on the Microsoft Connect website 2 times.

  • The first time it is marked as fixed because the user chooses to use a workaround, which is to pass a White Space then remove it from within the package. Read more
  • The Second time it is marked as "Won't fixed" because the Microsoft team is focusing on other issues Read more

Also a similar question is asked on MSDN network and it is solved with a workaround.

SO it seems that this is a software bug, that it is not solved yet. You may use a workaround for that.

Solution 2:[2]

I've solved with this workaround tested on SSMS version 17.9.1

Change back the value to an empty string and then move to the next step by clicking "Next" button.

Then the job can be saved without any errors.

Solution 3:[3]

I have a few clarifications for what we've noticed compared to your situation:

When I then try to save the Job Step properties, I get an error dialog with the message in my subject line.

If I put a non-empty value back in for that parameter, the job step can be saved.

We've noticed that if you click Cancel after receiving the error message, SQL Server will still persist a change to your job configuration but it will be corrupted. You can see the exact command it has saved in msdb via:

EXEC msdb.dbo.sp_help_jobstep @job_id=N'<job guid>', @step_id=<step>

It was persisting a parameter argument of the form:

... /Par "\"$Project::paramName\""; /Par...

which would then cause SQL Server Agent to fail on job start with:

Argument ""$Project::paramName";" for option "parameter" is not valid.

This is a nightmare, as everything looks fine in the UI but the job fails. If you then proceed to re-open the configuration UI editor and click OK, the correct command will be persisted (i.e. that parameter will be removed from the command) and the job will run correctly.

The above has been observed on version:

Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ).

Solution 4:[4]

I've tested and observed the behaviour of this bug, and the following works for me: 1. Set a design-time default value of a blank string for your parameter. 2. Configure your job and parameter values as required, but don't override the value for your blank parameter (leave it as is). It should appear with the value "NULL" in italics in the configuration panel. 3. Save the job and you're done.

The only way you can work a blank value is by ensuring it is based on your design-time default and don't override with a blank string. If you attempt to override your parameter with a blank value, it configures the command text for your job script to include an empty parameter value, and this is what causes the job script to not save or the job to fail if you manually script the create job statements. If your parameter is used only to compare in a WHERE clause, then you should be fine to pass a space instead of a blank, unless your comparison logic involves converting your values to binary, where white-space characters will have an impact.

Solution 5:[5]

I had a similar problem trying to get an empty string into a variable using DTEXEC. What worked for me was to convert the literal value to an expression that evaluates to an empty string. I used TRIM(" "). So after navigating the mess of escape characters, I ended up with something like this:

DTEXEC /SERVER . /ISSERVER "\SSISDB\MyFolder\MyProject\MyPackage.dtsx" /SET \Package.Variables[User::StringVar].EvaluateAsExpression;True /SET \Package.Variables[User::StringVar].Expression;""TRIM(\"" \"")""

Solution 6:[6]

Before setting job step values, configure the package in Integration Services Catalog in Set Parameter Value window.

enter image description here

To do that, go to Integration Services Catalog -> Folder -> Project -> Package -> Right Click: Configure. Set the empty string in the Edit value box:

enter image description here

You can set the empty string in the job step parameters afterwards.

Solution 7:[7]

Stored procedure sp_update_jobstep works for me - Script out the job and update the step with the parameter values removed.

Before: @command=N'/ISSERVER ""\SSISDB\MyFolder\MyProject\MyPackage.dtsx"" /SERVER MyServer /ENVREFERENCE 109 /Par MyParm;""CBSLoad_Daily"" /Par ""$ServerOption::LOGGING_LEVEL(Int16)"";1 /Par ""$ServerOption::SYNCHRONIZED(Boolean)"";True /CALLERINFO SQLAGENT /REPORTING E'

EXEC msdb.dbo.sp_update_jobstep 
     @Job_name = N'MyJob'
   , @Step_name = N'MyJobStep'
   , @Step_id = 2
   , @Command =
   N'/ISSERVER "\"\SSISDB\MyFolder\MyProject\MyPackage.dtsx\"" /SERVER MyServer /ENVREFERENCE 109 /Par MyParm; /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'

Solution 8:[8]

I got the error using SQL Server 2019 with SSMS 18.11.1, while trying to schedule a SSIS package that had long strong passwords with special characters (something like “kCkPav`LGY8PYD>W#g'+4PE*”).

I solved the issue by using a somehow less cryptic password with just one dash as a special character in the middle, but still long and strong enough (such as “m6sq43RxqyunLs3-AzcrGWM”). Here:

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Alberto Spelta
Solution 3 Community
Solution 4 Brendon
Solution 5 EdH
Solution 6 Raf
Solution 7 Salahuddin Ahmed
Solution 8 heroesch

Related Questions