Publish to SQL Azure fails with 'Cannot drop t

Question

I've got an SQL Project (.sqlproj) in my solution with target platform 'Microsoft Azure SQL Database V12'.

Recently I've added an external data source and several external tables targeting this data source.

ExternalCSVLists.sql file:

CREATE EXTERNAL DATA SOURCE [ExternalCSVLists] WITH
(  
    TYPE = RDBMS,
    LOCATION = 'location.windows.net',
    DATABASE_NAME = '$(CSVLists)',
    CREDENTIAL = RemoteConnectionCredential
)

Example of external table (IntegerListContent.sql file)

CREATE EXTERNAL TABLE [WebApp].[IntegerListContent] 
(  
     [ListId] INT,
     [Value] int 
)
WITH  
(  
    DATA_SOURCE = [ExternalCSVLists]
) 

First time publish went OK.

Now, when I publish again ( not having any changes done to either of external tables or data-sources), I receive the following error:

Dropping [ExternalCSVLists]... (415,1): SQL72014: .Net SqlClient Data Provider: Msg 33165, Level 16, State 1, Line 1 Cannot drop the external data source 'ExternalCSVLists' because it is used by an external table.

I've inspected the publish script and noticed that it attempt to drop-and-create the external data source. The external tables are skipped ( which is probably OK since I didn't change them ).

So,

1) why does it yield a drop external data source statement when all such data sources are identical to those already published

2) why does it ignore the dependent external tables then?

My publish settings are pretty much default (none of the options in 'Drop' tab are checked ). Thanks!

Answer 1

I hit the same issue; and unfortunately I could not figure out why this problem was created in the first place.

But the work around was to introduce a pre-deployment step that dropped ALL

• Tables AND
• Views

That referenced the External Data Source... I would have expected the DACPAC to perform this on its own; but I suppose we live in a non-perfect world.

Answer 2

I have also this problem. As a workaround you can also use: /p:DoNotDropObjectTypes=ExternalDataSources

But it works only if you have no changes in your external data source.

I opened a feedback case on microsoft. If you like you can support the case.