SqlPackage seems to escape right square bracket ( ] ) in variable value passed to .dacpac

I'm passing a variable to my .dacpac but the text received is not what I passed. Example command:

sqlpackage /v:TextTest="abc]123" /Action:Publish /SourceFile:"my.dacpac" /TargetDatabaseName:MyDb /TargetServerName:"."

My variable $(TextTest) comes out as "abc]]123" instead of the original "abc]123".

Is there anything I can do to prevent SqlPackage from corrupting my input variables before they are passed to the .dacpac scripts?

Unfortunately, I don't think there is a good answer. This appears to be a very old bug. I'm seeing references to this issue going back 10 years.

Example A: https://web.archive.org/web/20220831180208/https://social.msdn.microsoft.com/forums/azure/en-US/f1d153c2-8f42-4148-b313-3449075c612f/sql-server-database-project-sqlcmd-variables-with-closing-square-brackets

They mention a "workaround" in the post, but they link to a Microsoft Connect issue which no longer exists and is not available on archive.org.

My best guess is that the "workaround" is to generate the deploy script rather than publishing, and then manually modify the variable value in the script...which is not really a workaround if you are working on a build/release pipeline or any sort of automation.

I tried testing this to see if it would make any difference using Microsoft.SqlServer.Dac.DacServices.Publish() directly (via dbatools PowerShell module), but unfortunately the problem exists there as well.

I also tested it against every keyboard accessible symbol and that is the only character it seems to have a problem with.

Another option, though still not great, is to generate the deployment script, then execute it using SQLCMD.EXE.

So for example this would work:

sqlpackage /Action:Script `
           /DeployScriptPath:script.sql `
           /SourceFile:foobar.dacpac `
           /TargetConnectionString:'Server=localhost;Database=foobar;Uid=sa;Password=yourStrong(!)Password' `
           /p:CommentOutSetVarDeclarations=True

SQLCMD -S 'localhost' -d 'foobar' -U 'sa' -P 'yourStrong(!)Password' `
       -i .\script.sql `
       -v TextTest = "abc]123" `
       -v DatabaseName = "foobar"

• /p:CommentOutSetVarDeclarations=True - This setting is key, otherwise SQLCMD will be overridden by what's in the file. Just make sure you specify ALL variables, and not just the one you need. So open the file to look at what is commented out and make sure you are supplying what is needed.

It's not a great option...but it's at least scriptable and doesn't require manual intervention.