SSDT (SqlPackage) thread crashes with error when publishing

In this post, I will show you one of the scenarios when SSDT (SQL Server Data Tools) can fail unexpectedly while publishing database project to target server or preparation of changes script (Generate script), which is actually the first part or publish action.

Scenario

I had a solution with a few database projects. Visual Studio 2017 (Version 15.7.6) with SSDT (15.1.61901.09210). We were working on this project on and off making small changes in various databases. Not too much work, but then the deployment needs to be done. Having few environments (i.e. DEV, CI, UAT, PrePROD, PROD) it’s a bit challenging (and wrong!) to do this manually, even though with (PowerShell) script.
The best option is to prepare a fully automated deployment process in Azure DevOps. So, I did. That one was pretty simple, without additional tests, reports, etc. Baby steps, right? Just simple build pipeline and release pipeline for all databases in the solution in order to replace manual work with automating one.
Once I have done – build works perfectly, release… almost. Release pipeline contains several steps – one per database. Job executed only partially and stopped at one database:

Let’s double check the details:

That’s the error developers really do not like: saying that something is wrong… where SOMETHING is a keyword. Nothing else. No hints, no details.

Hence, I decided to return to the roots – Visual Studio. Solution rebuild – fine. When I was trying to deploy (publish) THAT database to THAT server, I’ve got the following error:

Object reference not set to an instance of an object.

This is a bug in SqlPackage.exe as the error is unhandled and that’s the reason why the process looks like running in Visual Studio (‘Data Tools Operations’ tab), but really – the thread under the hood crashed.

I started to investigate it deeper and the facts I found are the following:

• All databases from the solution exist on the server
• All other databases in the solution were capable to publish to THAT server – OK
• THAT database causes the error when trying to publish to THAT server – error
• Publish THAT database to ANOTHER server (environment) – works fine
• Publish THAT database to THAT server, but NEW database – works fine
• Cleaning solution (menu Build -> Clean solution) and rebuild doesn’t help – still the same error
• Closing Visual Studio, deleting *.dbmdl files for the database(s) didn’t help – still the same error
• It doesn’t matter whether you use a publish profile to deploy DB or publish action (right click on database node -> Publish) – still the same error

All factors indicate that the target server and other databases are not guilty. The problem only occurs with this particular database (and a specific server) when we try to generate a differential script. So the problem has to be somewhere in the differences between the project and the database on the server.

After a few hours of investigation, I found the exact differences, the exact SET OF OBJECTS that cause the problem.

Circumstances

The error happens in the case when:

1. Database project contained a table with partitions (schema+function)
2. The table is already deployed on a target server (database)
3. The table is excluded in SSDT (but schema+function are remaining)
4. You try to deploy DB without “deleting objects not exist in the source” (means the option is OFF)

Simply – SSDT can’t cope with that scenario and a thread crashes.

Solution/workaround

Interesting enough – all above must occurs. So, the workaround of this issue is “easy” (when you know what need to do – it’s often easy).
Do one of the following actions:

• Exclude partitions for that table from the project (not recommended if the table should exist on the database)
• Drop the table from target database (recommended if you don’t need it. In that case – do above action as well)
• Set option “deleting objects not exist in the source” (always recommended, but be careful with that)
• Include the excluded table in the database project

I agree that the described case is pretty unusual, but in our often-changing environments and code – that might happen. SSDT is a very good tool that no other databases have and generally speaking it is very useful for building database solutions. Unfortunately, when you found yourself in such a situation like this – you will waste a lot of time. The worst thing I really don’t like in such situations is lack of tip(s) in the error message. Would be good to have at least a small hint that could lead you in the right direction.

Thanks for reading!

Updated @ 08/11/2019
This post is already part of the whole series about SSDT.