search

microsoft / DACExtensions

DACExtensions contains samples that extend Data-Tier Applications using DacFx. These samples include deployment contributors and static code analysis rules that can be used with Visual Studio as well as examples of how to use the DacFx public mode
MIT License
125 stars 41 forks source link

Publishing a DACPAC to a database ignores master.dacpac same directory #47

Open t-mxcom opened 4 years ago

t-mxcom commented 4 years ago

Introduction

My solution makes use of the Microsoft.SqlServer.DacFx.x64 (v150.4826.1) NuGet-package to programmatically publish a DACPAC, built using Visual Studio 2019 (Version 16.6.2) and SQL Server Data Tools (16.0.62006.03190), to an existing database.

The DACPAC references types contained in the master.dacpac file which is referenced in the model.xml file as follows:

<CustomData Category="Reference" Type="SqlSchema">
    <Metadata Name="FileName" Value="C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\COMMUNITY\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\100\SQLSCHEMAS\MASTER.DACPAC" />
    <Metadata Name="LogicalName" Value="master.dacpac" />
    <Metadata Name="ExternalParts" Value="[master]" />
    <Metadata Name="SuppressMissingDependenciesErrors" Value="True" />
</CustomData>

Before starting the publish operation, both DACPACs are copied to the same directory:

C:\MyDirectory\MyDatabase.dacpac
C:\MyDirectory\master.dacpac

Issue

When publishing the DACPAC using C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe, the master.dacpac of the same directory (MyDirectory) is used and the operation works.

But when publishing the same file using the DacFx assemblies, they try to load master.dacpac from exactly the same directory as specified in the reference definition in the metadata (C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\COMMUNITY\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\100\SQLSCHEMAS\MASTER.DACPAC) which of course doesn't exist on a production machine. Thus the operation failes.

25.06.2020 22:59:48 - E - Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
25.06.2020 22:59:48 - E - Warning SQL72025: No file was supplied for reference master.dacpac; deployment might fail. When package was created, the original referenced file was located C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\COMMUNITY\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\100\SQLSCHEMAS\MASTER.DACPAC.
25.06.2020 22:59:48 - E -  ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: An error occurred while adding references.  Deployment cannot continue.
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.ThrowIfErrors(String message, ErrorManager errors, Object category)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointPackage.DeploymentEndpointReferenceLoader.OnLoaded(ErrorManager errors)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.DacpacHeaderLoader.Load(ErrorManager errors)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointPackage.OnLoad(ErrorManager errors, DeploymentEngineContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.PrepareModels()
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator()
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(Action`1 msgHandler)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
25.06.2020 22:59:48 - E -    --- Ende der internen Ausnahmestapelüberwachung ---
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.InternalPublish(DacPackage package, String targetDatabaseName, PublishOptions publishOptions, Boolean executePlan)

(Timestamps were added by my application)

How to solve this?

Is there a way to specify additional directories, where the DacFx assemblies look for referenced DACPACs? Is there something like a "reference resolving event" that can be handled by my application returning the path or a stream to the referenced DACPAC? Are there any other options, that make the DacFx assemblies incorporate the source DACPAC's directory when searching for referenced DACPACs?

Is there anything else that SqlPackage.exe does additionally which makes the reference discovery work and which I could also add to my application?

Thank you for your help!

ErikEJ commented 4 years ago

You could use the code here to delete and add the master reference in the .dacpac before publishing: https://github.com/GoEddie/Dacpac-References/blob/master/src/DacpacHeaderParser.Tests/WriteHeaderTests.cs

t-mxcom commented 3 years ago

Thanks for the link! Of course, modifying the DACPAC file before the deployment is a way of solving the problem - but I'd rather call it a "workaround" than a "solution". Especially as it works with sqlpackage.exe without prior modification.

Do you know, where I can file this issue so it will receive attention from the DacFx developers?

sajanmittal commented 3 years ago

I am facing the same issue too. I have some views who are referring to the master database information schema views. Please let me know if any solution is identified to fix this one.