Deploy SSDT database project with external tables fails
During the deployment of a SSDT Database Project with external tables on Azure, deployment fails with this error message:
2018-07-10T17:23:00.8539155Z Dropping [EDS]...
2018-07-10T17:23:01.7970626Z An error occurred while the batch was being executed.
2018-07-10T17:23:01.8200128Z Updating database (Failed)
2018-07-10T17:23:02.1289225Z ##[error]*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 33165, Level 16, State 1, Line 1 Cannot drop the external data source 'EDS' because it is used by an external table.
Error SQL72045: Script execution error. The executed script:
DROP EXTERNAL DATA SOURCE [EDS];
- The Target of SQL Project is target platform 'Microsoft Azure SQL Database V12'.
- A Azure SQL Database Deployment (Extended) Tasks in Version v 1 was used
- and these additional SqlPackage.exe Arguments:
/p:IncludeTransactionalScripts=True /p:IncludeCompositeObjects=True /p:GenerateSmartDefaults=True /p:BlockOnPossibleDataLoss=False /p:DropIndexesNotInSource=False /p:IgnorePartitionSchemes=True /p:IgnoreObjectPlacementOnPartitionScheme=True /p:DoNotDropObjectTypes=PartitionFunctions;PartitionSchemes /df:sqlpackage.log /p:DropObjectsNotInSource=True
I know I can drop all external table in a predacpac file, but it would be great if sqlpackage.exe could handle it by it self, because we would like to use /p:IncludeTransactionalScripts=True to support a more reliable deployment, but changes outside of it (i.e. in a predacpac) can leave the database in an undefined state in case of an error.
Peter Horst commented
We still have the problem. Our Workaround is to execute an extra script before the database deployment and dropping all external tables and external sources, but this is not a nice workaround, because we always will drop these objects, even if we have no changes on the external tables.
We trying to reach zero downtime during deployment, but this throws a monkey wrench in the works.
Chaitanya Phadke commented
Did you manage to resolve this issue? I am facing a similar error. I want to ignore all such warnings and go ahead with the upgrade. Tried /p:BlockOnPossibleDataLoss=False to no avail.