DACPAC schema compare runs before pre-deployment scripts during publish

Question

When publishing a dacpac with sqlpackage.exe, it runs Schema Compare first, followed by pre-deployment scripts. This causes a problem when, for instance, you need to drop a table or rename a column. Schema Compare was done before the object was modified and the deployment fails. Publish must be repeated to take the new schema into account.

Anyone have a work-around for this that does not involve publishing twice?

Answer 1

Gert Drapers called it as pre-pre-deployment script here

Actually it is a challenge. If you need to add non-nullable and foreign key column to a table full of data - you can do with a separate script only.

If you are the only developer - that is not a problem, but when you have a large team that "separate script" has to be somehow executed before every DB publish.

The workaround we used:

• Create separate SQL "Before-publish" script (in DB project) which has a property [Build action = None]
• Create custom MSBuild Task where to call SQLCMD.EXE utility passing "Before-publish" script as a parameter, and then to call SQLPACKAGE.EXE utility passing DB.dacpac
• Add a call of the custom MSBuild Task to db.sqlproj file. For example:

<UsingTask 
        TaskName="MSBuild.MsSql.DeployTask" 
        AssemblyFile="$(MSBuildProjectDirectory)\Deploy\MsBuild.MsSql.DeployTask.dll" />

<Target Name="AfterBuild">
    <DeployTask 
        Configuration="$(Configuration)" 
        DeployConfigPath="$(MSBuildProjectDirectory)\Deploy\Deploy.config" 
        ProjectDirectory="$(MSBuildProjectDirectory)" 
        OutputDirectory="$(OutputPath)" 
        DacVersion="$(DacVersion)">
    </DeployTask>
</Target>

MsBuild.MsSql.DeployTask.dll above is that custom MSBuild Task.

Thus the "Before-publish" script could be called from Visual Studio.

For CI we used a batch file (*.bat) where the same two utilities (SQLCMD.EXE & SQLPACKAGE.EXE) were called.

The final process we've got is a little bit complicated and should be described in a separate article - here I mentioned a direction only :)