search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.53k stars 896 forks source link

Import bacpac failed - Object reference not set to an instance of object #9522

Closed cw-engineering closed 4 years ago

cw-engineering commented 4 years ago

When trying to import .bacpac file to local SQL server, the process always fails with the following message:

Import bacpac failed . | MyDb Error: Object reference not set to an instance of object.

There are no additional details, just this message. I've tried with two different bacpac files, both created from Azure SQL server, both failed with the same error message.

Current workaround: Use SSMS to import bacpac file.

Extension: SQL Server Dacpac (microsoft.dacpac) Extension version: 1.2.0 Azure Data Studio version: azuredatastudio 1.15.1 (83544eba2504f564d785534cc780d2073feec2cc, 2020-02-15T05:03:16.545Z) OS version: Windows_NT x64 10.0.16299

System Info |Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz (8 x 3408)| |GPU Status|2d_canvas: enabled
flash_3d: enabled
flash_stage3d: enabled
flash_stage3d_baseline: enabled
gpu_compositing: enabled
metal: disabled_off
multiple_raster_threads: enabled_on
oop_rasterization: disabled_off
protected_video_decode: unavailable_off
rasterization: enabled
skia_renderer: disabled_off
surface_control: disabled_off
surface_synchronization: enabled_on
video_decode: enabled
viz_display_compositor: enabled_on
viz_hit_test_surface_layer: disabled_off
webgl: enabled
webgl2: enabled| |Load (avg)|undefined| |Memory (System)|15.79GB (9.50GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|
kisantia commented 4 years ago

Can you try exporting using the .NET Core version SqlPackage.exe and use the /df parameter to get the log file and please share it? This uses the same backend that ADS uses to do DacFx operations.

Example export command: sqlpackage.exe /Action:Export /ssn:[server] /sdn:[database] /su:[user] /sp:[password] /tf:"C:\Temp\database.bacpac" /df:sqlpackage-export.log

cw-engineering commented 4 years ago

Hi @kisantia,

The error occurred when importing a bacpac file into SQL server running locally previously exported from Azure SQL, and not when exporting one.

I've thus run the following command:

sqlpackage.exe /a:Import /df:"bacpac.log" /sf:"1-DbAbc.bacpac" /tcs:"Server=.;Database=1-DbAbc;Integrated Security=true"

The log file is attached. The error in the log file:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2020-03-23T11:55:13 : SqlPackage failed unexpectedly
 Exception: System.NullReferenceException: Object reference not set to an instance of an object.
   at 
[bacpac.log](https://github.com/microsoft/azuredatastudio/files/4369147/bacpac.log)
Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateFragment(Int32 operation, IModelElement element) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.cs:line 408
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateSteps(Int32 operation, IModelElement element) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.cs:line 297
   at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildDependencyOrderedSteps(Int32 operation, List`1 classOrder, List`1 operationOrder, Dictionary`2 changes, Boolean preserveGraphs, Dictionary`2& relating, Dictionary`2& related) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\Analyzers\PlanMediator.cs:line 1365
   at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildOperations() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\Analyzers\PlanMediator.cs:line 803
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnGeneratePlan() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.cs:line 340
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.GeneratePlan(List`1 drops) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.cs:line 890
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.BuildPlan() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.cs:line 1746
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.CreatePlan() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.Controller.cs:line 126
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass16_1.<CreatePlanInitializationOperation>b__1() in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 151
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Dac\Logging\OperationLogger.cs:line 48
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass16_0.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 104
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\Operation.cs:line 72
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\ReportMessageOperation.cs:line 44
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 63
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 63
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 470
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 26
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 826
   at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacImportOptions importOptions, Nullable`1 cancellationToken) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 2677
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoImportBacpacOperation(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 850
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 212
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 156
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 50
kisantia commented 4 years ago

We had a regression in the 18.4.1 release where there's the error “Object reference not set to an instance of an object.” when importing a bacpac with a user with an external login. Can you try importing using SqlPackage 18.4? If that doesn't work, we'll need a bacpac that repros this to debug.

markhobson commented 4 years ago

I had the same issue and downgrading to 18.4 worked for me.

kisantia commented 4 years ago

Please try upgrading ADS and the dacpac extension. The fix was included in the ADS 1.16 release.

markhobson commented 4 years ago

I believe I'm on the latest version and it still fails.

Azure Data Studio:

Version: 1.16.1 Commit: 16801c522df68e63218b39ccb5b4a7431f9bf667 Date: 2020-03-23T22:09:40.250Z VS Code: 1.42.0 Electron: 7.1.11 Chrome: 78.0.3904.130 Node.js: 12.8.1 V8: 7.8.279.23-electron.0 OS: Linux x64 5.3.0-42-generic

Extension:

Name: SQL Server Dacpac Id: microsoft.dacpac Description: Manage data-tier applications Version: 1.3.0 Publisher: Microsoft

kisantia commented 4 years ago

@markhobson Looks like it only got fixed for deploying/importing to Azure. We will also fix this for on prem. Thanks!

kisantia commented 4 years ago

first fix didn't work, so now targeting to get this fixed for June release.

MatthewSteeples commented 4 years ago

I don't know if this helps, but we had a similar problem importing a DB from SQL Azure to localdb, and needed to pass in the following parameter into SqlPackage /p:ExcludeObjectTypes="Logins;ServerRoleMembership;ServerRoles;Credentials;Rolemembership;DatabaseRoles;Users"

Our problem was that we have AzureAD roles in the database, and SqlPackage hit an NRE when trying to import it

kisantia commented 4 years ago

@MatthewSteeples glad to hear you were able to use a workaround. We have a fix and it will be in the June ADS release and the next sqlpackage release. Previously, sqlpackage would convert AAD users to users without logins when importing to a platform that didn't external logins, but that got regressed late last year.

bardiente commented 4 years ago

I don't know if this helps, but we had a similar problem importing a DB from SQL Azure to localdb, and needed to pass in the following parameter into SqlPackage /p:ExcludeObjectTypes="Logins;ServerRoleMembership;ServerRoles;Credentials;Rolemembership;DatabaseRoles;Users"

Our problem was that we have AzureAD roles in the database, and SqlPackage hit an NRE when trying to import it

@MatthewSteeples how did you pass the command into SqlPackage? I get an error "'ExcludeObjectTypes' is not a valid argument for the 'Import' action." when trying to import my bacpac from an Azure SQL export.

SqlPackage.exe /Action:Import /SourceFile:"[bacpacfile]" /TargetDatabaseName:"[dbname]" /TargetServerName:"[targetserver]" /p:ExcludeObjectTypes="Logins;ServerRoleMembership;ServerRoles;Credentials;Rolemembership;DatabaseRoles;Users"

I'm running SqlPackage v18.5

MatthewSteeples commented 4 years ago

We're publishing a dacpac file, rather than importing a bacpac

SqlPackage.exe /action:publish /sf:"db.dacpac" /tcs:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=Ledgerscope;Integrated Security=true;" /p:ExcludeObjectTypes="Logins;ServerRoleMembership;ServerRoles;Credentials;Rolemembership;DatabaseRoles;Users"

Hadn't twigged that this wasn't the same use case, but could well be the same issue

bardiente commented 4 years ago

We're publishing a dacpac file, rather than importing a bacpac

SqlPackage.exe /action:publish /sf:"db.dacpac" /tcs:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=Ledgerscope;Integrated Security=true;" /p:ExcludeObjectTypes="Logins;ServerRoleMembership;ServerRoles;Credentials;Rolemembership;DatabaseRoles;Users"

Hadn't twigged that this wasn't the same use case, but could well be the same issue

Thanks @MatthewSteeples for the clarification. In my particular scenario of importing the bacpac I ended up downgrading my SqlPackage to v18.4 and the standard import command worked fine.

SqlPackage.exe /Action:Import /SourceFile:"[bacpacfile]" /TargetDatabaseName:"[dbname]" /TargetServerName:"[targetserver]"