How to skip external data source references while doing export/Restore of an Azure SQL DB

Issue 

We recently encountered a support case where a customer was getting an error message while trying to restore a bacpac to an Azure SQL DB as the bacpac file was pointing to Prod external data source. The customer wanted to know how to skip the external data source while taking bacpac or ignore it while doing the restore at the target. 

Error 

Here is the error message the cx was getting while restoring the bacpac: 

TITLE: Microsoft SQL Server Management Studio 

------------------------------ 

Could not import package. 

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 16539, Level 16, State 3, Line 4 Operation failed since the external data source 'https://mystoage.blob.core.windows.net/account-new' has underlying storage account 

Workaround 

We reproduced the error internally in our lab and recommended the following changes to the customer’s sqlpackage commands: 

1. extract with /p:ExtractAllTableData=true 

1. publish with /p:ExcludeObjectType=ExternalDataSources 

After following the recommendations above, the cx was able to restore the DB export successfully.  

References 

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-extract?view=sql-server-ver16 

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16 

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver16#command-line-syntax