Sql-server – SQLPackage.exe not picking up changes

sql serverssdt

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

Best Answer

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.

Related Question