Special character in Sensitive SSIS Package Parameter String renders package invalid

Question

I have a Sensitive String Parameter on an SSIS Package that is used to store a password for a remote server.

However, the Job Agent throws an error whilst configuring the Package Parameters of that step when the string value contains a curly brace:

Microsoft SQL Server Management Studio: Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

The package works when it is directly executed in Visual Studio Data Tools, or when the { is removed from the password, or when the String is configured as a regular, non-sensitive string. I cannot simply change the password since it is provided by a 3rd party and really would like to keep the configuration as is.

I believe this problem is related to SQL Server 2016 problems with SSIS String parameters.

Is there a way to fix this? I tried to escape the { but to no avail.

Answer 1

I have some passwords with curly braces and have never experienced this issue. One thing that I am doing differently is using Environment Variables. All my connection strings, passwords, file paths, and other parameters that change from environment to environment are in environment variables. Each parameter in the SSIS package is then mapped to the corresponding environment variable.

So to try my method out:

1. create an environment
2. create variables that correspond to the parameters
3. add a reference to the environment in the project
4. on the configuration tab of the job, select the environment

If you have any questions about any of those steps, let me know and I can try to provide some additional details.