Sql Agent Job SSIS Package Parameter value with cu

问题:

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.

The Server is SQL Server 2014.

回答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

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

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

回答2:

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.