search

rr-wfm / MSBuild.Sdk.SqlProj

An MSBuild SDK that provides similar functionality to SQL Server Data Tools (.sqlproj) projects
MIT License
406 stars 46 forks source link

Add support for package references to other databases #51

Closed jeffrosenberg closed 4 years ago

jeffrosenberg commented 4 years ago

In SSDT, when adding a reference to another database project, you're given three options:

  1. Same database (i.e. the referenced project will be combined with yours)
  2. Same server, different database (i.e. the referenced project represents another database on the same server on which our project has a dependency)
  3. Different server, different database (i.e. the referenced project represents another database on a different server on which our project has a dependency)

Currently in MSBuild.Sdk.SqlProj, all package references are treated as type 1 -- they are combined into a single database. I'm requesting support for type 2 references -- dependencies on a different database.

My company has several databases with functions that reference objects in the master database, and thus require a type 2 reference to master -- we need MSBuild to know that we're referencing objects from master, but those objects shouldn't be added to our database. Here's what the sqlproj-style reference looks like:

<ArtifactReference Include="$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\120\SqlSchemas\master.dacpac"> <HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\120\SqlSchemas\master.dacpac</HintPath>
  <SuppressMissingDependenciesErrors>True</SuppressMissingDependenciesErrors>
  <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
</ArtifactReference>

Currently, when testing with one of these projects, the build does succeed with a reference to master. However, an attempt to actually deploy the final dacpac causes SqlPackage to throw an error (I use SqlPackage directly for deployments, rather than the deploy options recently added to this project). I'll include that error at the bottom of this issue.

Because the DacFX API to reference other packages isn't publicly exposed, I don't know how much of a lift this is. I'm hoping that, having already built some hooks into that functionality, you might have some insight into this?

SqlPackage deploy error for reference (it doesn't seem very helpful to me):

Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2020-09-04T13:28:17 : SqlPackage failed unexpectedly
 Exception: System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlScriptDomGenerator.CreateStatementGenerator.GenerateCreateAggregateStatement(IModelElement modelElement) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlScriptDomGenerator.CreateStatementGenerator.cs:line 5297
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlScriptDomGenerator.CreateStatementGenerator.GenerateStatement(IModelElement modelElement) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlScriptDomGenerator.CreateStatementGenerator.cs:line 235
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlScriptDomGenerator.TryGenerateScriptDom(IModelElement element, ScriptDomOperation operation, TSqlScript& script) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlScriptDomGenerator.cs:line 224
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlScriptDomGenerator.GenerateScriptDom(IModelElement element, ScriptDomOperation operation) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlScriptDomGenerator.cs:line 193
   at 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 445
   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 344
   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 894
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.BuildPlan() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.cs:line 1755
   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 155
   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 108
   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.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 474
   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 838
   at Microsoft.SqlServer.Dac.DacServices.InternalPublish(DacPackage package, String targetDatabaseName, PublishOptions publishOptions, Boolean executePlan) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 695
   at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.<>c__DisplayClass10_2.<DoDeployAction>b__0(DacServices service) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\DacServiceUtil.cs:line 181
   at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.ExecuteDeployOperation(CommandLineArguments cmdlineArgs, String connectionString, String filePath, String modelFilePath, MessageWrapper messageWrapper, Boolean sourceIsPackage, Boolean targetIsPackage, Boolean authType, String tenantId, String customAuthArgs, String accessToken, String[] referencePaths, Func`1 generateScriptFromPackage, Func`2 generateScriptFromDatabase) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\DacServiceUtil.cs:line 413
   at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.DoDeployAction(DeployArguments deployArgs, Action`1 writeError, Action`2 writeMessage, Action`1 writeWarning, CancellationToken cancellationToken, CommandLineArguments cmdlineArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\DacServiceUtil.cs:line 149
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoDeployActions(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 360
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 223
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 160
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 54
jmezach commented 4 years ago

My guess would be that the exception your seeing is caused by the deployment trying to deploy things in the master database because they are part of the model. Looking at the stack trace I'm guessing you're using SqlPackage.exe to deploy it. Have you tried using dotnet publish? Maybe that will give us some more insights into what's failing.

I'll have to do some digging around the DacFx model to see how we would go about referencing another .dacpac that is supposed to represent another database. I'm sure it can be done, it just means figuring out how ;).

jeffrosenberg commented 4 years ago

Yes, that was my interpretation as well -- SqlPackage is trying to pull the master database into the model. I can give dotnet publish a try and see if that provides more information.

ErikEJ commented 4 years ago

Please share a repro .csproj and a .dacpac.

During publish, both the master.dacpac and the mydb.dacpac should be present/available.

jeffrosenberg commented 4 years ago

@ErikEJ I can confirm that both dacpacs are present and available. I actually created my own Nuget package that pushes master.dacpac into our Nuget repository in order to ensure that's the case. I may not be able to post repro files until Tuesday, as it will take a bit of work and I have other activities I need to get completed at work today first.

@jmezach I tried running via dotnet publish, but just got the same error without even a stack trace. Nothing useful in the binlog either, just "Object reference not set to an instance of an object".

I'll provide some repro files as soon as I'm able.

jeffrosenberg commented 4 years ago

Okay, this was actually quicker to put together than I anticipated. I've pushed the source code, as well as the binaries (.dacpac and .nupkg files) to the following repo: https://github.com/jeffrosenberg/db-test

On the branch bug/master-reference, I have two commits:

Commit 1: No master reference, dotnet publish works fine Commit 2: Includes master reference, dotnet publish throws "Object reference not set to an instance of an object".

EDIT: Uploaded .zip files in case that's easier: without-master.zip with-master.zip

jmezach commented 4 years ago

I did some reflection and I think what we need to do is add additional metadata here. Specifically we need to set the ExternalParts metadata. My guess is that this would be set to the value of the DatabaseVariableLiteralValue item metadata value on the ArtifactReference that is shown in the initial post. @jeffrosenberg If you could share a .dacpac created by a similar .sqlproj that uses that ArtifactReference that would be awesome so that I can verify my assumption here.

If that is indeed the case I think it shouldn't be too hard to pass that metadata value along to the DacpacTool so that we can use it when adding the reference. We'll probably need to change the reference arguments format, but that shouldn't be a problem.

ErikEJ commented 4 years ago

This is CustomData for a master.dacpac reference in model.xml based on a .sqlproj:

        <CustomData Category="Reference" Type="SqlSchema">
            <Metadata Name="FileName" Value="C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\ENTERPRISE\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\130\SQLSCHEMAS\MASTER.DACPAC" />
            <Metadata Name="LogicalName" Value="master.dacpac" />
            <Metadata Name="ExternalParts" Value="[master]" />
            <Metadata Name="SuppressMissingDependenciesErrors" Value="False" />
        </CustomData>
jmezach commented 4 years ago

@jeffrosenberg I've published a beta version that I believe will fix this issue. Would you mind giving it a try on your projects? You'll need to update the SDK version in your project file and then add the DatabaseVariableLiteralValue attribute with the appropriate value to your PackageReference and then rebuild your project.

I ran into an issue with the CI pipeline that I need to investigate. I don't think it is related to the change, but I want to make sure, so that's why I've published a beta version first.

jeffrosenberg commented 4 years ago

Wonderful, thanks so much for looking at this so quickly! I've run a test with the beta version, and can confirm a successful publish. Thanks again!

jmezach commented 4 years ago

No problem. You've already done so much on this as well.

Which reminds me, would you consider becoming a Collaborator on this project? Usage is definitely increasing so it would be great to have an extra pair of hands to help review PR's and triage issues, etc.

jeffrosenberg commented 4 years ago

@jmezach I'd love that, thank you!

jmezach commented 4 years ago

@jeffrosenberg Awesome! You should have received an invite. Please note that we are requiring 2 factor authentication so if you haven't set that up you'll need to do that in order to accept the invite.

jmezach commented 4 years ago

Version 1.6.0 is now up on NuGet.org which contains the support for this. Thanks again @jeffrosenberg for reporting this.