Error: Azure SQL DacpacTask Step(Action) in Azure build pipeline

Question

Scenario-
We have created a build pipeline for creating the sql database for particular customers from the azure pipeline. We are using the sql DACPAC action to create the sql database. In some customers, it runs successfully. For some customers, it gives an error as given below:
Results -
Warning SQL72016: The column DivisionId on table [dbo].[Stg_Division] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. [error]or all rows or mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option. Error SQL72045: Script execution error.  The executed script: IF EXISTS (SELECT TOP 1 1            FROM   [dbo].[Stg_Division])     RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)         WITH NOWAIT;
We checked this error in the database from VSTS but this given error is not found there and also tried to manually publish the database in the local sql server management. It is working fine there. We are not able to track down the route cause of the issue we need your help to sort it out. Let me know what else you need from our side. we are available for the call as well. we are in the IST time-zone. Availability- 9:30 am to 7 pm IST 

Answer 1

@v-Govind Chouksey Thanks for reaching out! Seems this question more inclined to Azure DevOps. I would kindly suggest you to post your question here where Azure DevOps experts can assist you effectively for your request.

Answer 2

Is this script deploying an entirely new database, or is supposed to upgrade an existing database?

In the latter case, the error is quite self-explanatory - you appear to have a problem with schema drift.

For the empty database case, it is a little more surprising. Then again, I would not expect an script for a new database to have an ALTER TABLE statement at all.