SqlPackage Object reference not set to an instance of an object

Question

When I attempt to deploy DACPACs via SqlPackage.exe, I encounter the error below :

An unexpected failure occurred: Object reference not set to an instance of an object..

Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.ReverseEngineerPopulators.Sql90TableBaseColumnPopulator`1.InsertElementIntoParent(SqlColumn element, TElement parent, ReverseEngineerOption option)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.ReverseEngineerPopulators.ChildModelElementPopulator`2.CreateChildElement(TParent parent, EventArgs e, ReverseEngineerOption option)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.ReverseEngineerPopulators.ChildModelElementPopulator`2.PopulateAllChildrenFromCache(IDictionary`2 cache, SqlReverseEngineerRequest request, OrdinalSqlDataReader reader, ReverseEngineerOption option)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.ReverseEngineerPopulators.TopLevelElementPopulator`1.Populate(SqlReverseEngineerRequest request, OrdinalSqlDataReader reader, ReverseEngineerOption option)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.ExecutePopulators(ReliableSqlConnection conn, IList`1 populators, Int32 totalPopulatorsCount, Int32 startIndex, Boolean progressAlreadyUpdated, ReverseEngineerOption option, SqlReverseEngineerRequest request)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.ExecutePopulatorsInPass(SqlReverseEngineerConnectionContext context, ReverseEngineerOption option, SqlReverseEngineerRequest request, Int32 totalCount, Tuple`2[] populatorsArray)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.PopulateBatch(SqlReverseEngineerConnectionContext context, SqlSchemaModel model, ReverseEngineerOption option, ErrorManager errorManager, SqlReverseEngineerRequest request, SqlImportScope importScope)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineer.PopulateAll(SqlReverseEngineerConnectionContext context, ReverseEngineerOption option, ErrorManager errorManager, Boolean filterManagementScopedElements, SqlImportScope importScope, Boolean optimizeForQuery, ModelStorageType modelType)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.ImportDatabase(SqlReverseEngineerConstructor constructor, DeploymentEngineContext context, ErrorManager errorManager)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.OnLoad(ErrorManager errors, DeploymentEngineContext context)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.PrepareModels()
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator()
   at Microsoft.SqlServer.Dac.DacServices.<>c__DisplayClass21.<CreateDeploymentArtifactGenerationOperation>b__1f(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.GenerateDeployScript(DacPackage package, String targetDatabaseName, DacDeployOptions options, Nullable`1 cancellationToken)
   at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.<>c__DisplayClasse.<DoDeployAction>b__4(DacServices service)
   at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.ExecuteDeployOperation(String connectionString, String filePath, MessageWrapper messageWrapper, Boolean sourceIsPackage, Boolean targetIsPackage, Func`1 generateScriptFromPackage, Func`2 generateScriptFromDatabase)
   at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.DoDeployAction(DeployArguments parsedArgs, Action`1 writeError, Action`2 writeMessage, Action`1 writeWarning)
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoDeployActions(CommandLineArguments parsedArgs)
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args)
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args)

Below is the command I run:

SET vardeploy2=/Action:Script
set varBlockOnDriftParameter=/p:BlockWhenDriftDetected=False 

"SSDTBinaries\SqlPackage.exe" %vardeploy2% %varBlockOnDriftParameter% /SourceFile:"dacpacs\DBName.dacpac" /Profile:"Profiles\%1.DBName.Publish.xml" >> Log.txt 2>>&1

I deploy to a SQL Server 2008 R2. The SqlPackage.exe version is 11.0.2820.0.

The issue is intermittent, which suggests it's not related to the DACPAC being deployed or the destination database's schema. My best guess is that something about the state of the database is causing the problem.

Still, I haven't been able to identify anything unusual at the time of the failures.

When recreating the issue locally, using schema locks results in a different error message.

Has anyone know of a solution?

Answer 1

Upgrade to a more resent SQL Server Data Tools.