How to set DriftOptionBlockDataLoss when using Powershell cmdlets

CopperStarSystems

We are using the Sql Change Automation Powershell cmdlets in our build/release pipeline on a VSTS Hosted VS2017 agent.

We have a recent commit that drops a column from an existing table, however when we execute the New-DatabaseReleaseArtifact cmdlet, it fails with this warning:

Drift analysis: These changes will NOT be applied because DriftOptionBlockDataLoss=True (see 'C:\Users\VssAdministrator\AppData\Local\Temp\DLM Automation\stqvpuxg.zsb\artifacts\[REDACTED]_DriftSyncScript.sql' for details). Changes have been detected that could result in data loss for the following table object(s): [REDACTED].

Is there a way to specify the DriftOptionBlockDataLoss variable when invoking this cmdlet?

Thanks!

Jessica R

Hi @CopperStarSystems!

You can workaround this issue by adding the <DriftOptionBlockDataLoss>False</DriftOptionBlockDataLoss> tag to your .sqlproj file.

​

CopperStarSystems

Thanks, I'll try that.  That said, it would be nice to be able to set this option from the cmdlet, there are certain environments where I'm OK with potential data loss, and others where I'm not

Jessica R

Thanks for that feedback! I've passed it on to our developers.

Jessica R

Hi @CopperStarSystems!

Just letting you know that we've got a feature request for the ability to enable DriftOptionBlockDataLoss from the cmdlet and it's logged in our tracking system as SCA-68. Thanks again for this suggestion!

I do just want to clarify though: the DriftOptionBlockDataLoss option actually applies just to the drift correction script (not the update script).