Publish DACPAC file using MSDeploy, UTF8 characters after script deployment lost

Question

I have a DACPAC file that was built into Visual Studio 2013 for an SSDT project. This SSDT project defines a post-deployment script designed to combine some static data into published tables, and one piece of data contains a copyright symbol.

Now when I publish the database through Visual Studio, the copyright symbol is saved and correctly merged into the target table. When I publish the same database (with the same dumppack and publish the profile) using MSDeploy, the copyright symbol is combined into the target database as "?" symbol. Similarly, when I use Action:Script instead of Action:Publish , the generated SQL script contains "?" not a copyright symbol.

The Visual Studio script seems to be generating UTF8 encoding, but the script that is baked into dacpac loses the UTF8 encoding. Does anyone have any ideas on how to get around this problem?

Answer 1

Does a string literal prefix with N mean that it contains a Unicode string? Is your column nchar as nchar or nvarchar ? The dacpac creation process can perform a conversion based on the difference that your data is declared as a non-unicode string. It is not surprising that the author’s character could not survive this transformation.

For more information on unicode and character strings, see https://msdn.microsoft.com/en-us/library/ms179899.aspx .

Answer 2

I had the same problem. Open this file in notepad and save it as unicode in the same folder to replace the old one. Then publish again. It should work.