1

As the title says, I get this error when trying to Publish using a publish profile XML:

DropExtendedPropertiesNotInSource conflicts with the selected DoNotDropObjectType ExtendedProperties

I have specified to drop objects in target that are not in source, but have excluded everything except for Tables and Synonyms. Here are the two lines I believe are related to this error:

<DropObjectsNotInSource>True</DropObjectsNotInSource>
<DoNotDropExtendedProperties>True</DoNotDropExtendedProperties>

Full publish profile below, with target database information removed.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<IncludeCompositeObjects>True</IncludeCompositeObjects>
<TargetDatabaseName>REDACTED</TargetDatabaseName>
<DeployScriptFileName>REDACTED.sql</DeployScriptFileName>
<TargetConnectionString>Data Source=REDACTED;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
<BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
<DropObjectsNotInSource>True</DropObjectsNotInSource>
<DoNotDropAggregates>True</DoNotDropAggregates>
<DoNotDropApplicationRoles>True</DoNotDropApplicationRoles>
<DoNotDropAssemblies>True</DoNotDropAssemblies>
<DoNotDropAsymmetricKeys>True</DoNotDropAsymmetricKeys>
<DoNotDropAudits>True</DoNotDropAudits>
<DoNotDropBrokerPriorities>True</DoNotDropBrokerPriorities>
<DoNotDropCertificates>True</DoNotDropCertificates>
<DoNotDropClrUserDefinedTypes>True</DoNotDropClrUserDefinedTypes>
<DoNotDropColumnEncryptionKeys>True</DoNotDropColumnEncryptionKeys>
<DoNotDropColumnMasterKeys>True</DoNotDropColumnMasterKeys>
<DoNotDropContracts>True</DoNotDropContracts>
<DoNotDropCredentials>True</DoNotDropCredentials>
<DoNotDropCryptographicProviders>True</DoNotDropCryptographicProviders>
<DoNotDropDatabaseAuditSpecifications>True</DoNotDropDatabaseAuditSpecifications>
<DoNotDropDatabaseRoles>True</DoNotDropDatabaseRoles>
<DoNotDropDatabaseScopedCredentials>True</DoNotDropDatabaseScopedCredentials>
<DoNotDropDatabaseTriggers>True</DoNotDropDatabaseTriggers>
<DoNotDropDefaults>True</DoNotDropDefaults>
<DoNotDropEndpoints>True</DoNotDropEndpoints>
<DoNotDropErrorMessages>True</DoNotDropErrorMessages>
<DoNotDropEventSessions>True</DoNotDropEventSessions>
<DoNotDropEventNotifications>True</DoNotDropEventNotifications>
<DoNotDropExtendedProperties>True</DoNotDropExtendedProperties>
<DoNotDropExternalDataSources>True</DoNotDropExternalDataSources>
<DoNotDropExternalFileFormats>True</DoNotDropExternalFileFormats>
<DoNotDropExternalTables>True</DoNotDropExternalTables>
<DoNotDropFileTables>True</DoNotDropFileTables>
<DoNotDropFilegroups>True</DoNotDropFilegroups>
<DoNotDropFullTextCatalogs>True</DoNotDropFullTextCatalogs>
<DoNotDropFullTextStoplists>True</DoNotDropFullTextStoplists>
<DoNotDropLinkedServerLogins>True</DoNotDropLinkedServerLogins>
<DoNotDropLinkedServers>True</DoNotDropLinkedServers>
<DoNotDropLogins>True</DoNotDropLogins>
<DoNotDropMessageTypes>True</DoNotDropMessageTypes>
<DoNotDropPartitionFunctions>True</DoNotDropPartitionFunctions>
<DoNotDropPartitionSchemes>True</DoNotDropPartitionSchemes>
<DoNotDropPermissions>True</DoNotDropPermissions>
<DoNotDropQueues>True</DoNotDropQueues>
<DoNotDropRemoteServiceBindings>True</DoNotDropRemoteServiceBindings>
<DoNotDropRoutes>True</DoNotDropRoutes>
<DoNotDropRoleMembership>True</DoNotDropRoleMembership>
<DoNotDropRules>True</DoNotDropRules>
<DoNotDropScalarValuedFunctions>True</DoNotDropScalarValuedFunctions>
<DoNotDropSearchPropertyLists>True</DoNotDropSearchPropertyLists>
<DoNotDropSecurityPolicies>True</DoNotDropSecurityPolicies>
<DoNotDropSequences>True</DoNotDropSequences>
<DoNotDropServerAuditSpecifications>True</DoNotDropServerAuditSpecifications>
<DoNotDropServerRoles>True</DoNotDropServerRoles>
<DoNotDropServerRoleMembership>True</DoNotDropServerRoleMembership>
<DoNotDropServices>True</DoNotDropServices>
<DoNotDropServerTriggers>True</DoNotDropServerTriggers>
<DoNotDropSignatures>True</DoNotDropSignatures>
<DoNotDropStoredProcedures>True</DoNotDropStoredProcedures>
<DoNotDropSymmetricKeys>True</DoNotDropSymmetricKeys>
<DoNotDropSynonyms>False</DoNotDropSynonyms>
<DoNotDropTableValuedFunctions>True</DoNotDropTableValuedFunctions>
<DoNotDropUserDefinedDataTypes>True</DoNotDropUserDefinedDataTypes>
<DoNotDropUserDefinedTableTypes>True</DoNotDropUserDefinedTableTypes>
<DoNotDropUsers>True</DoNotDropUsers>
<DoNotDropViews>True</DoNotDropViews>
<ExcludeUsers>True</ExcludeUsers>
<ExcludeLogins>True</ExcludeLogins>
<ProfileVersionNumber>1</ProfileVersionNumber>
<ExcludeFullTextCatalogs>True</ExcludeFullTextCatalogs>
<IgnoreIndexOptions>True</IgnoreIndexOptions>
<DoNotDropXmlSchemaCollections>True</DoNotDropXmlSchemaCollections>
</PropertyGroup>
</Project>
0

The documentation states that DropExtendedPropertiesNotInSource has a default value of true.

DropExtendedPropertiesNotInSource=(BOOLEAN 'True')
Specifies whether extended properties that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

So you need to set this to False in order to correct this problem.

Unfortunately, it seems like the SSDT user interface in Visual Studio is kind of trolling us, which is probably how you ended up in this situation.

By default, that DropExtendedPropertiesNotInSource is set to True in the UI (which aligns with the documentation - good 👍):

Screenshot of SSDT advanced publish settings showing "Drop Extended Properties Not In Source" is checked

When you check the "Drop objects in target but not in source" option, that bottom section gets disabled, and the list of "opt out" checkboxes is enabled:

Screenshot of SSDT advanced publish settings showing "Drop objects in target but not in source" is checked, and the "drop extended properties not in source" option has been disabled

This might make you feel like those options have been set to false, but they have not been - bad 👎

You'll need to manually uncheck them (first uncheck "drop objects in target but not in source", then uncheck the options at the bottom, then re-check "drop objects in target..."):

Screenshot of SSDT advanced publish settings showing correctly checked and unchecked options

If you're not using the UI, then these are the elements that need to be added to the publish XML file:

<DropExtendedPropertiesNotInSource>False</DropExtendedPropertiesNotInSource>
<DropConstraintsNotInSource>False</DropConstraintsNotInSource>
<DropDmlTriggersNotInSource>False</DropDmlTriggersNotInSource>
<DropIndexesNotInSource>False</DropIndexesNotInSource>

Unfortunately I can't reproduce the error you're getting (I'm testing with Visual Studio 2019 version 16.4.0 and SSDT 16.0.61911.11100), but I'm hopeful that adding those explicit "False" options will correct the problem.

2
  • 1
    I can confirm that those 4 XML lines are added to the publish profile using the UI in VS 2017. This does indeed fix the error-- even an error I had earlier: "The property DoNotDropObjectTypes may only be set when DropObjectsNotInSource is set to true." Very odd, but I appreciate the insight. I'll most likely open a bug report to Microsoft regarding this. – seanw_pc Dec 26 '19 at 21:11
  • @seanw_pc Oh excellent, I'm glad that worked! And yeah, I agree this should be reported to Microsoft. – Josh Darnell Dec 26 '19 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.