SQLPackage.exe not picking up changes

All we need is an easy explanation of the problem, so here it is.

I’m trying to get SQLPackage for schema compare working from CMD line. Have to compare a master db to 300 live dbs. All 2014 SQL and none registered as data-tier applications. PowerShell and 3rd party tools are not an option at this time.

I’ve followed the scripts in the first answer here:
https://stackoverflow.com/questions/20673516/command-line-api-for-schema-compare-in-ssdt-sql-server-database-project

I can successfully create .DACPAC files from my source and my targets.

If I add some objects (1 table, 3 views) to “Test_B” database, the compare script does not show those differences when I compare the master to either the database itself or to the .dacpac that was generated. BUT, if I run a SSDT schema compare from VS, it does pick them up and will create a .sql script with the expected DROP statements.

So the question: What am I doing wrong at the cmd line that Visual Studio/SSDT is doing right? Missing a parameter? Wrong order of things?

#Compare DACPACs

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.Test_B.dacpac /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql


#Compare master dacpac to database

sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\ACACalculationDB.JTA.v7.master.dacpac /tsn:US1213113W1\SQL2014 /tdn:ACACalculationDB.JTA.v7.Test_B /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\ACACalculationDB.JTA.v7.Test_B.sql

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The issue is that the default deployment options when using Script or Deploy actions are set up to avoid dropping objects so as to minimize the risk of data loss. Schema Compare, on the other hand, is targeted at an interactive mode where the actions (Add, Delete, Update) are much clearer so this doesn’t occur. To generate the Drop syntax as expected, add the property shown below (from the SqlPackage.exe documentation) to your call.

Property: /p:DropObjectsNotInSource= {True | False}

Default: False

Description: Specifies whether objectsthat do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply