Search code examples
sql-server-2012dacpacpublish-profiles

SQL Server Database Publish Profile not using configured property


We have a database project for our SQL Server 2012 database. This database has a few SQL CLR assemblies which are very rarely updated that I wish to exclude from an automated dacpac deployment. I created a publish profile with the following settings.

Publish Profile

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <TargetConnectionString></TargetConnectionString>
        <AllowIncompatiblePlatform>True</AllowIncompatiblePlatform>
        <CreateNewDatabase>False</CreateNewDatabase>
        <IgnorePermissions>True</IgnorePermissions>
        <DropObjectsNotInSource>False</DropObjectsNotInSource>
        <IgnoreUserSettingsObjects>True</IgnoreUserSettingsObjects>
        <IgnoreLoginSids>True</IgnoreLoginSids>
        <IgnoreRoleMembership>True</IgnoreRoleMembership>
        <ExcludeObjectTypes>"Assemblies;AsymmetricKeys"</ExcludeObjectTypes>
    </PropertyGroup>
</Project>

The most important part of this profile is the tag ExcludeObjectTypes which per the MS documentation should exclude those types from the deployment. When I try to do a publish in Visual Studio with this profile it is still generating create/alter scripts for the Asymmetric Keys and Assemblies. How can I exclude these types?

Publish Profile Generated Script

...
GO
PRINT N'Creating Asymmetric Key [MyKey]...';

GO
CREATE ASYMMETRIC KEY [MyKey]
    AUTHORIZATION [dbo]
    WITH ALGORITHM = RSA_1024;

GO
PRINT N'Altering Assembly [MyAssembly]...';

GO
ALTER ASSEMBLY [MyAssembly]
    DROP FILE ALL;
...

Solution

  • The syntax for SQL Server 2012 must be different from new versions of SQL Server which are linked in the documentation. When I opened the profile through the wizard and click Advanced.. -> Ignore Tab and select Exclude assembles and Exclude asymmetric keys

    enter image description here

    Then it creates entries in the publish profile with the below syntax:

        <ExcludeAssemblies>True</ExcludeAssemblies>
        <ExcludeAsymmetricKeys>True</ExcludeAsymmetricKeys>
    

    This definition takes effect properly.