SSDT Prevent Synonym Dropping on Publish

Background

We are using SQL Server Data Tools (SQL Project) that targets SQL Server 2012 which uses DACPAC to publish to our different environments.

Problem

Whenever we publish SSDT, the synonyms are being dropped and re-created based on the generated publish script. We want to prevent this from happening since we have a stored procedure that is responsible for setting the correct synonym targets per environment.

GO PRINT N'Dropping [dbo].[MY_SYNONYM]...'; GO DROP SYNONYM [dbo].[MY_SYNONYM]; GO PRINT N'Creating [dbo].[MY_SYNONYM]...'; GO CREATE SYNONYM [dbo].[MY_SYNONYM] FOR [MY_DEFAULT_TARGET]; 

I've tried setting the build action of the synonyms to anything else other than Build but it causes a problem with the compiler and results to an unsuccessful build.

Our main goal is to prevent synonym re-creation for synonyms that didn't change targets. We know that we can handle this on our stored procedure by leveraging sys.synonyms for comparisons. Any help is appreciated, thank you.

EDIT:

I can see the ff on the Advanced Publish Settings but we do not want to enable Drop objects in target but not in source. We want the synonyms in the project to not get re-created during publish.

1 Answer

Thanks to @Bill Jetzer's recommendation I was able to find the ff in the publish profile:

Apparently there's an option /p:ExcludeObjectType=Synonyms for SqlPackage if we want to do this using command line.

After ticking the exclude synonyms, in the publish profile the line below was added:

<PropertyGroup> ... <ExcludeSynonyms>True</ExcludeSynonyms> </PropertyGroup>