1

I have a SSIS package deployed to the SSISDB catalog. The package is configurable with parameters for values such as FTP details.

I have noticed that attempting to create a SQL agent job for this package fails when any of the parameter values contains a left curly brace {

Unfortunately, one of the password parameters contains a left curly brace.

The question is: without resorting to replacing place holders for that character in the actual package (feels dirty), can this character be escaped or the job saved in a different way that will allow left curly braces?

Here is an image of the error message you get when attempting to save the agent job step. error message you get when attempting to save the agent job step with parameters containing values with curly brace

The Server is SQL Server 2014.

JonM
  • 1,314
  • 10
  • 14
  • Is storing the password in the Agent parameter settings *really* the best idea here? Anyone would be able to access it there. – Thom A Feb 08 '18 at 16:20
  • Workaround for this is to use `Execute Package Task`, or try to run the package with `exec ssisdb.catalog.create_execution` – Ven Feb 08 '18 at 16:20
  • @Larnu the password parameter is set to sensitive so (AFAIK) it shouldn't be accessible to anyone. But I am totally open to suggestions – JonM Feb 08 '18 at 16:21
  • Creating an environment as suggested by [Jeremy J.](https://stackoverflow.com/users/10229561/jeremy-j) in this [answer](https://stackoverflow.com/a/52486172/8291949) solved the same issue running SQL Server 2016 for me. – wp78de Sep 24 '18 at 20:32
  • @wp78de brilliant thanks, if you add that as an answer then I can mark it as the solution! – JonM Sep 27 '18 at 15:53
  • Okay, not a problem. – wp78de Sep 27 '18 at 19:13

2 Answers2

1

SSIS 2012 and newer seems to have a problem with Sensitive Package Parameters that contain special characters like a left curly brace. A workable alternative is to put the sensitive strings as Variables in a corresponding Environment and load the values from there as suggested by Jeremy J here.

First, create a new Environment in the Integration Services Catalog on an existing SSISDB/{Project?}/Environment node

enter image description here

Then, configure the sensitive string variable that contains the offending character there
and add a reference to the Environment in the Package.

enter image description here

Finally, on the Configuration tab of the Job, select the environment, and use the environment variables.

enter image description here

wp78de
  • 18,207
  • 7
  • 43
  • 71
0

Well I've tested and confirmed that I can include Curly Braces in parameters when I create the job with a script.

The lazy way to do this (IOW, the way I did it):

Create your job the way you are doing, but take the Curly Brace character out of your parameter value.

Open SSMS, right click on the job, and choose "Script Job As > Drop & Create...".

Modify the resulting script, adding in the Curly Brace character.

Your parameter will be in a snippet of the sp_add_jobstep @command and you'll want it to look something like this:

/Par "\"MyParameterName\"";"\"example{value\""

Execute.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thanks Tab, unfortunately this prevents the package from actually running. When I script out the step/job without a curly brace I get the following command line: `@command=N'/ISSERVER "\"\SSISDB\Feeds\TestFeed\TestParameters.dtsx\"" /SERVER testserver /Par ftpHostname;"\"example.server.com\"" /Par ftpPassword;"\"aaaaaaaaaaaaa\"" /Par "\"ftpPortNumber(Int32)\"";12345 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', ` and the package runs (until it needs to authenticate) but adding the brace prevents job run – JonM Feb 09 '18 at 09:43
  • The agent runner complains that the argument `has mismatched quotes. The command line parameters are invalid. The step failed.` – JonM Feb 09 '18 at 09:44
  • You might also find with your example (as I did) that when attempting to edit the parameters through the UI you will find the values blank. – JonM Feb 09 '18 at 09:52
  • Sounds like it's acting as though a curly brace is a special character that needs to be escaped, but I find nothing in the documentation about escaping special characters in sp_add_jobstep. I'd open a ticket with Microsoft. – Tab Alleman Feb 09 '18 at 14:48
  • As a work-around, could you store the password as a project (or package) parameter and let the job just take the default instead of specifying the parameter in the job at all? – Tab Alleman Feb 09 '18 at 14:51