rr-wfm/msbuild.sdk.sqlproj

An MSBuild SDK that provides similar functionality to SQL Server Data Tools (.sqlproj) projects

msbuild.sdk.sqlproj's Issues

Incremental build support

Currently the CoreCompile target defined in Sdk.targets will always be run regardless of whether any changes were made to the input files. But given the same set of inputs the resulting .dacpac should be the same, so I think it makes sense to add incremental build support here.

I think we should be able to do that by adding Inputs and Outputs attributes to the target. It is just a matter of defining the right set of inputs. Obviously that includes the input files, but we'll also want to rebuild the .dacpac if a PackageReference is being added or when any of the model properties change. Perhaps it is the entire set of commandline arguments passed to BuildDacpac (or DacpacTool once #33 is merged) that if unchanged should result in the same output. But I'm not sure if we can actually use that as Inputs.

Or maybe the correct set of Inputs would be the input files (.sql) as well as the project file itself (the .csproj). If any of those files are newer than the .dacpac we should produce a new .dacpac.

Log verbosity

Hey, I did sed on sql files to get rid of sqlcmd variables, while the sqlproj project seems to work just fine csproj is reporting one problem which at the moment is impossible to trace due to the size of the project:

Is there any way to increase log verbosity?

Deploying to older version of SQL Server error message is not clear

I have a SQL Server 2016 database server.
I have a project file that includes <SqlServerVersion>Sql130</SqlServerVersion>.
dotnet publish works great!

Sql120 also works. However, with Sql140, publishing fails with this error message. It's not obvious that it is due to a version mismatch.

EXEC : error : Deployment of database 'Prediction' failed: An error occurred during deployment plan generation. Deployment cannot continue. [C:\dev\Prediction\Prediction.fsproj]
C:\Users\User\.nuget\packages\msbuild.sdk.sqlproj\1.8.1\Sdk\Sdk.targets(220,5): error MSB3073: The command "dotnet "C:\Users\User\.nuget\packages\msbuild.sdk.sqlproj\1.8.1\Sdk\../tools/netcoreapp3.1/DacpacTool.dll" deploy -i "C:\dev\Prediction\bin\Debug\netstandard2.0\/Prediction.dacpac" -tsn "localhost\MyServer" -tdn "Prediction"   -p IncludeCompositeObjects=true  " exited with code -1. [C:\dev\Prediction\Prediction.fsproj]

[Question] How can I load the modified project back into VS2019 for editing?

Like many people trying to build dotnet core projects on linux, that include SQL server projects, I ended up here after some internet searching and research.

Like others, I'm editing in Visual Studio, checking into GitSVN then having JenkinsCI build my project on a linux target.

Before finding this project, this was my issue

After following the instructions on your main page, and replacing my project file with that shown (The sql server project was created from the VS2019 template) I now find I can build the project from the command line using "dotnet build", but I can no longer load the project into Visual Studio 2019 for editing, when I try I just get the following:

How can I load the project into VS for editing, but still use your project to enable me to build my solution without the presence of the project breaking the build.

Support fast up-to-date check

Looks like the up-to-date check is actually a Visual Studio feature that is separate from the MSBuild logic. I found this document that describes this feature. We might be able to implement that, although I'm not sure if this will solve the problem at hand.

Originally posted by @jmezach in #63 (comment)

Is it possible to add support for GenerateCreateScript?

It seems that something in the vein of the example below could be added to PackageBuilder or in Extensions, that could be called based on a new property added to PackageOptions.

public void GenerateCreateScript(FileInfo dapacFile)
{
  using (var package = DacPackage.Load(dap.FullName))
  using(var file = File.Create("some-parameterized-name.sql"))
  {
     DacServices.GenerateCreateScript(file, package, "test");
               
  }
}

Not super familiar with the underlying apis here so there may be a better way using than DacServices or a reason that it's not feasible for this project.

In any case, thanks for this awesome project!

Problem loading the project on Visual Studio

I am having an issue using this tool. I have added the Project SDK and targetFramework property to a SQL Server project, as per your guidelines and although it builds and generates the dacpac file on VS code and on the build server, I can't load the project on visual studio. I get the following error when I try to reload the project:

It seems to be a problem with some missing software on my machine, but I am also wondering if I am setting this up properly. Any hint on what I am doing wrong?

Incremental build ignores changes to pre/post deploy script

It seems that the incremental build support added in #34 ignores any changes made to pre- and/or post-deploy scripts. They are not part of the inputs, thus the .dacpac isn't being regenerated. This is probably also the case for the .refactorlog file added in #45.

Improve error messages for validation

Before the .dacpac is written the DacpacTool will perform a validation of the model. This can yield errors and warnings which are currently written to the console. These are picked up by Visual Studio and show up in the Errors List as you would expected. However, they are not correlated to the source file nor the source location on which the error occurs. This data is probably available in the Validate method on the PackageBuilder, but we simply don't expose it properly.

Post/Pre deployment scripts support

Seems like these are not supported.

I've played a little bit with appending such scripts, seems like simple appending of the .sql to resulting dacpac (zip) should work (https://the.agilesql.club/2019/03/how-can-we-merge-multiple-dacpacs-into-one/), however, I'm not sure how to tackle sqlcmd syntax support.
Running sqlcmd -i postdeployment_entryfile.sql -e -r >postdeploy.sql generates correct file, but bundling sqlcmd within the target project may be difficult?

How to handle master database reference

I have two projects

• MyProject.Database.Mssql - classic Sqlproj project to develop the database
• MyProject.Database.Mssql.Build - to build the DACPAC

My Sqlproj project has a reference to the master database. Otherwise Visual Studio would throw some errors like

Severity Code Description Project File Line Suppression State
Error SQL71501 Error validating element [dbo].[ViewName]: SqlView: [dbo].[ViewName] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[TableA].[rows], [dbo].[TableB].[rows] or [sys].[partitions].[rows]. MyProject.Database.Mssql C:\s\MyProject\src\MyProject.Database.Mssql\EXEC 1

But since the building project now imports the SQL scripts, Visual Studio throws the corresponding error messages in the build project, because there is no reference to the master database.

How do you deal with this? I could not find any reference in the documentation.

My build project:

<Project Sdk="MSBuild.Sdk.SqlProj/1.6.0">
    <!-- https://github.com/rr-wfm/MSBuild.Sdk.SqlProj -->

    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>SqlAzure</SqlServerVersion>
        <!-- For additional properties that can be set here,
        please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <PropertyGroup>
        <!-- Refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#publishing-support for supported publishing options -->
    </PropertyGroup>

    <ItemGroup>
        <Content Include="..\..\db\MyProject.Database.Mssql\dbo\**\*.sql" />
    </ItemGroup>

</Project>

Environment: Microsoft Visual Studio Enterprise 2019 Version 16.8.0 Preview 3.1
.NET 5 RC 1

Help setting up a debugging environment

I am testing out the feasibility of using this SDK to convert our existing SSDT projects. I can setup a blank new project with no problems, but when I try to import all of our sql files from one of our existing projects I get an error:

Determining projects to restore...
  All projects are up-to-date for restore.
  The system cannot execute the specified program.
C:\Users\bmcclure\.nuget\packages\msbuild.sdk.sqlproj\1.1.0\Sdk\Sdk.targets(153,5): error MSB3073: The command "dotnet 

Followed by a lot of text. This project has ~1400 sql files in the folder structure, so I removed a bunch of files (1000) to try a build with fewer files. This appears to be actually running msbuild, and is now throwing errors and I can see from the stack trace that these are getting thrown from this sdk nuget:

 Unable to find input file C:\xxxx\LoadDiagnosiFileTable.sql (Parameter 'inputFile')
     at MSBuild.Sdk.SqlProj.BuildDacpac.PackageBuilder.AddInputFile(FileInfo inputFile) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/BuildDacpac/PackageBuilder.cs:line 60

I can infer some stuff from this error message, and I can read the code at this line, but what I would like to do is setup a breakpoint in VS and run dotnet build and force dotnet/nuget to use my local build instead of the version from nuget so I can debug/step through why this is failing so that I can make better issues/contributions to this code. This dotnet sdk development is new to me, do you have any suggested reading/links that could help put me on the right track for developing/debugging this?

Referencing other dacpacs fails on macOS with 'Unable to find reference file' ArgumentException

I'm working on a POC for adopting this project to manage my organization's database builds to make them cross-platform. Our builds typically combine 3-5 dacpacs into a composite database project. When attempting to build a composite project that includes a PackageReference on a Mac, I received an error that the SDK was 'Unable to find reference file'.

Here's my .csproj file (unnecessary properties removed):

<Project Sdk="MSBuild.Sdk.SqlProj/1.1.0">

  <!-- Database configuration properties -->
  <PropertyGroup>
    <!-- ... -->
  </PropertyGroup>

  <!-- File includes / excludes -->
  <!-- MSBuild.Sdk.SqlProj Sdk defaults to including all *.sql files -->
  <ItemGroup>
    <Content Remove="Scripts/**/*.sql" />
    <Content Remove="target/**/*.sql" />
    <PostDeploy Include="Scripts/Script.PostDeployment.sql" />
    <PreDeploy Include="Scripts/Script.PreDeployment.sql" />
  </ItemGroup>

  <!-- References to dacpac dependencies -->
  <ItemGroup>
    <PackageReference Include="Base" Version="7.6.0" />
  </ItemGroup>
</Project>

Here's the error I receive on performing a build or pack:

Unhandled exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
   ---> System.ArgumentException: Unable to find reference file /Users/jrosenberg/.nuget/packages/Base/7.6.0\tools\Base.dacpac (Parameter 'referenceFile')
     at MSBuild.Sdk.SqlProj.BuildDacpac.PackageBuilder.AddReference(FileInfo referenceFile) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/BuildDacpac/PackageBuilder.cs:line 32
     at MSBuild.Sdk.SqlProj.BuildDacpac.Program.BuildDacpac(PackageBuilderOptions options) in /home/runner/work/MSBuild.Sdk.SqlProj/MSBuild.Sdk.SqlProj/src/BuildDacpac/Program.cs:line 57
     --- End of inner exception stack trace ---
     at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
     at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
     at System.Delegate.DynamicInvokeImpl(Object[] args)
     at System.Delegate.DynamicInvoke(Object[] args)
     at System.CommandLine.Invocation.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
     at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseParseErrorReporting>b__20_0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass15_0.<<UseHelp>b__0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass22_0.<<UseTypoCorrections>b__0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__21_0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseParseDirective>b__19_0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseDebugDirective>b__11_0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__10_0>d.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass13_0.<<UseExceptionHandler>b__0>d.MoveNext()

I'm pretty sure the issue is the backslashes in the path: '/Users/jrosenberg/.nuget/packages/Base/7.6.0\tools\Base.dacpac'. I notice that the Sdk.targets folder currently has a mix of forward- and backslashes. I've been able to fix this locally; I will send a pull request shortly.

Announcement: Video introduction

For anyone looking for a video introduction to this project you can now watch this dotNetFlix episode in which I go through getting set up and some of the things you can do.

DacpacTool invocation fails on Windows if the maximum command length is exceeded

Apparently there's a limit to the length of a command sent at the Windows command line (8191 characters, possibly up to 32767 in some situations). Because DacpacTool is called via the command line, and each included file is sent as a separate argument, projects with either (a) a lot of objects or (b) very long paths to each object will eventually exceed this limit and fail.

I ran into this limitation and was able to work around it by creating a symbolic link to shorten my paths, but that's a workaround at best and projects with a large number of objects won't be able to do that.

One possible solution to the problem would be to pass the list of included objects in a temporary file, rather than via the CLI.

Dotnet publish does not work when DACPAC output name is changed

As described in #65 it's possible to change the name of the DACPAC output by setting the AssemblyName in the project file. However changing the output name breaks the dotnet publish command. This is because the publish command tries to use the DACPAC file based on the project name.

C:\Users\rvink\.nuget\packages\msbuild.sdk.sqlproj\1.7.0\Sdk\Sdk.targets(199,5): error MSB3073: The command "dotnet "C:\Users\rvink\.nuget\packages\msbuild.sdk.sqlproj\1.7.0\Sdk\../tools/netcoreapp3.1/DacpacTool.dll" deploy -i "C:\Users\rvink\source\repos\TestProject\TestDatabase\bin\Debug\netstandard2.0\/TestDatabase.dacpac" -tsn "(local)" -tdn "RandomDatabaseName" -p CreateNewDatabase=False -p IncludeCompositeObjects=true " exited with code 1. [C:\Users\rvink\source\repos\TestProject\TestDatabase\TestDatabase.csproj]

This is my project file:

<Project Sdk="MSBuild.Sdk.SqlProj/1.7.0">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>Sql140</SqlServerVersion>
	<AssemblyName>RandomDatabaseName</AssemblyName>
	<TargetDatabaseName>RandomDatabaseName</TargetDatabaseName>
        <!-- For additional properties that can be set here, please refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <PropertyGroup>
        <!-- Refer to https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#publishing-support for supported publishing options -->
    </PropertyGroup>
</Project>

Occasional issue during CI

It sometimes happens that CI fails due to SqlPackage trying to deploy the .dacpac to the container, but the container hasn't yet fully started. I believe there is a way to wait for the container to start before the job runs, but I'll need to dive in to see how that works exactly.

postdeploy script appears to be loaded as SQL object script

Remove documented limitation on SQLCMD merging

LIMITATION: Currently there is no support for including other scripts from the pre- or post deployment script using the :r OtherScript.sql syntax. See this issue for more details.

didn't noticed any actual issues about that, I've tried to create my own project and it's failed coz of that problem.
Is it working now or not?
Also attached my project, maybe I did something wrong
DatabaseTest.zip

Append newline + GO to post and pre deploy scripts

I am folder comparing with WinMerge the same project built by VS and this.

Announcement: Master branch is now protected

This is just a heads-up to let everyone know that we've just setup branch protection for the master branch to avoid accidental pushes to that branch. New code changes should be submitted through pull requests that require at least 2 reviewers and must also pass the CI build.

@ErikEJ @jeffrosenberg Tagging you I though this will be important for you ;).

Adding an assembly registration to DB

How do I correctly add an assembly to db project?
For example I have an empty project

<Project Sdk="MSBuild.Sdk.SqlProj/1.9.0">
    <PropertyGroup>
        <TargetFramework>netstandard2.1</TargetFramework>
        <SqlServerVersion>Sql150</SqlServerVersion>
    </PropertyGroup>
</Project>

and I'm trying to create new assembly with sql script

CREATE ASSEMBLY [Test]
FROM 0x4D5A90000300000004000000FFFF000....
WITH PERMISSION_SET = UNSAFE
GO

when I try to build the project, I receive:
AddReference.sql(2, 6): [SQL70557] This assembly is corrupt or not valid.
I have this error with any assembly, I even try the Microsoft.SqlServer.Types.
So I must be doing something wrong.

Bug in deployement script saving

System.ArgumentException: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.
at System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.Compression.WrappedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.Data.Tools.Schema.Sql.Packaging.ZipWrappingStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at MSBuild.Sdk.SqlProj.BuildDacpac.PackageBuilder.WritePart(FileInfo file, Package package, String path) in C:\Code\MSBuild.Sdk.SqlProj\src\BuildDacpac\PackageBuilder.cs:line 280
at MSBuild.Sdk.SqlProj.BuildDacpac.PackageBuilder.AddPrePostScripts(FileInfo pre, FileInfo post, FileInfo outputFile) in C:\Code\MSBuild.Sdk.SqlProj\src\BuildDacpac\PackageBuilder.cs:line 76
at MSBuild.Sdk.SqlProj.BuildDacpac.Program.BuildDacpac(PackageBuilderOptions options) in C:\Code\MSBuild.Sdk.SqlProj\src\BuildDacpac\Program.cs:line 89

SqlCmdVariables should be in a single section

Expected:

            <CustomData Category="SqlCmdVariables" Type="SqlCmdVariable">
		<Metadata Name="DbReaderPassword" Value="" />
		<Metadata Name="DbUserPassword" Value="" />
	</CustomData>

Actual:

            <CustomData Category="SqlCmdVariables" Type="SqlCmdVariable">
		<Metadata Name="DbReaderPassword" Value="" />
	</CustomData>
	<CustomData Category="SqlCmdVariables" Type="SqlCmdVariable">
		<Metadata Name="DbUserPassword" Value="" />
	</CustomData>

(Have not tested actually using the variables, blocked on #19 )

Add support for ProjectReference

While MSBuild.Sdk.SqlProj supports PackageReference for referencing NuGet packages and automatically include their embedded .dacpac's as part of the build we do not currently support ProjectReference. In a .sqlproj this is typically defined as:

<ProjectReference Include="..\Database1\Database1.sqlproj">
  <Name>Database1</Name>
  <Project>{b4a1acc8-f726-499e-a0d5-955e146dc10a}</Project>
  <Private>True</Private>
  <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
  <DatabaseSqlCmdVariable>Database1</DatabaseSqlCmdVariable>
</ProjectReference>

In the case of SDK-style projects we can probably get rid of the Project metadata since those GUID's aren't used anymore. I'm not entirely sure what the Name and DatabaseSqlCmdVariable metadata is used for though. The Private metadata probably controls whether the .dacpac of the project being referenced is being copied to the output folder of the project that has the reference, which shouldn't be too hard to implement. The SuppressMissingDependenciesErrors might be a little bit more tricky and will require some research.

We'll also need to resolve the path of the .dacpac for the referenced project. I'm hoping that we can already get that from MSBuild somehow since it supports project to project references already.

Error when "restore database from URL" encountered in sql files

I followed along with the instructions at https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html and it was going okay until the build encountered an azure restore database xxx from url = 'xxx' statement, at which point it errored:

Adding /home/tim/repo/.../Restore_v1.0.sql to the model
Unhandled exception: System.Reflection.TargetInvocationException:
Exception has been thrown by the target of an invocation.
 ---> Microsoft.SqlServer.Dac.Model.DacModelException: Add or update objects failed due to the following errors:
EXEC : error SQL46010: Incorrect syntax near URL.
[/home/tim/repo/.../Database.Build.csproj]

The restore database from url option is documented here: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15#support-for-restore-arguments and I see there is a matching option on backup database to

Thanks for the project!

TSQLT install

• how would we include tsqlt as part of this build?
• I'm trying to get tsqlt unit test framework to work w/ a dotnet core build on a linux machine for devops pipelines such as github actions

troubleshooting

• if I could do a dacpac of tsql then I could include that for the models but that doesn't appear to be supported. such as from https://github.com/GoEddie/AdventureworksCI/tree/v0.2/src/Test/References
• I've been experimenting w/ https://github.com/lastlink/SQLCover/tree/f/tsql and trying to get tsql to work

<ItemGroup>
    <PreDeploy Include="..\..\..\..\tSQLt_V1.0.5873.27393\SetClrEnabled-linux.sql" />
  </ItemGroup>
  <ItemGroup>
    <!-- <Content Include="..\..\..\..\tSQLt_V1.0.5873.27393\tSQLt.class.sql" /> -->
  </itemGroup>

• when I just include it, the file itself raw doesn't work. I suppose I could initialize a database and then export a new version fo the scripts.

SqlCmdVariable support

Seems like there is no way to create dacpac that accepts params:

you only need to have sql like that:
INSERT INTO $(TasksDb).[schema].[table] ...

so you can pass the variable during runtime:
sqlpackage.exe /Action:Publish /SourceFile:pkg.dacpac /Target(..) /v:TasksDb=Tasks

btw such params are present in sqlproj as well:

    <SqlCmdVariable Include="TasksDb">
      <DefaultValue>Tasks</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>

Support for merging SQLCMD scripts

In version 1.1.0 we've added support for pre- and post deployment scripts (see #9). Unfortunately we do not yet support including other scripts from those scripts using the :r OtherScript.sql syntax.

Some initial work has been done to support this (see feature/sqlcmd-file-merging branch), but it has some limitations in that it doesn't work cross-platform. This is due to the fact that the SQL libraries we're currently using seems to uppercase the path, which doesn't work on platforms that have a case-sensitive file system (ie. Linux). Ideally we would like to keep this library cross-platform, so we'll need to figure out how to work around this.

[Question] Is there a way to set up the project to only build if source files change?

At the moment, if I set up the project then run a build it will say "1 succeeded".

If I run the build again without changing any files, I'd like the build to be skipped (i.e. "1 up-to-date" instead of "1 succeeded" in Visual Studio)

Is there a way to do this, or is it not possible, because we always need to reproduce the dacpac file?

SqlServerVersion not documented

Used but not shown in any links from readme: https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.model.sqlserverversion

Investigate support for .NET 5 SDK

While working on #29 I noticed that things weren't working on my local machine with the .NET 5 preview SDK installed. We'll probably want to add .NET 5 SDK's to the CI build so that we can see what's going wrong there.

Change Name of DACPAC Output

In my .csproj, <TargetDatabaseName>DBName</TargetDatabaseName> determines what my database will be called when I run dotnet publish. It doesn't influence what the the dacpac file is named when I run dotnet build. How can I change the name of the dacpac?

Support linked files

Following project build a dacpac, but with not tables in model.xml:

<Project Sdk="MSBuild.Sdk.SqlProj/1.0.0">
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <RecoveryMode>Simple</RecoveryMode>
  </PropertyGroup>
  <ItemGroup>
    <None Include="..\..\Database1\Database1\Table1.sql" Link="Table1.sql" />
  </ItemGroup>
</Project>

In addition, Publish fails:

1>------ Build started: Project: ConsoleApp1, Configuration: Release Any CPU ------
1>Using package name ConsoleApp1 and version 1.0.0
1>Setting property RecoveryMode to value Simple
1>Using SQL Server version Sql150
1>Writing model to C:\Users\Erik\Source\Repos\ConsoleApp7\ConsoleApp1\obj\Release\netstandard2.0\ConsoleApp1.dacpac
1>ConsoleApp1 -> C:\Users\Erik\Source\Repos\ConsoleApp7\ConsoleApp1\bin\Release\netstandard2.0\ConsoleApp1.dacpac
2>------ Publish started: Project: ConsoleApp1, Configuration: Release Any CPU ------
2>dotnet C:\Users\Erik\.nuget\packages\msbuild.sdk.sqlproj\1.0.0\Sdk\../tools/netcoreapp3.1/BuildDacpac.dll -o "obj\Release\netstandard2.0\ConsoleApp1.dacpac" -n ConsoleApp1 -v 1.0.0 -sv Sql150   -p RecoveryMode=Simple
2>Using package name ConsoleApp1 and version 1.0.0
2>Setting property RecoveryMode to value Simple
2>Using SQL Server version Sql150
2>Deleting existing file C:\Users\Erik\Source\Repos\ConsoleApp7\ConsoleApp1\obj\Release\netstandard2.0\ConsoleApp1.dacpac
2>Writing model to C:\Users\Erik\Source\Repos\ConsoleApp7\ConsoleApp1\obj\Release\netstandard2.0\ConsoleApp1.dacpac
2>ConsoleApp1 -> C:\Users\Erik\Source\Repos\ConsoleApp7\ConsoleApp1\bin\Release\netstandard2.0\ConsoleApp1.dacpac
2>Manifest file not found at 'C:\Users\Erik\Source\Repos\ConsoleApp7\ConsoleApp1\ConsoleApp1.nuspec'
========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========
========== Publish: 0 succeeded, 1 failed, 0 skipped ==========

Announcement: Transfer of ownership

This is not really an issue, but more of an announcement to let everybody know that Im considering transferring the ownership of this repository to my employers GitHub Organization. There are a couple of reasons for that:

1. We are actively using this package in our own projects. In fact I originally set out to build it just for us, but I figured that it could be useful for other people too so I decided to open source it. At the time my employers GitHub Organization didn't even exist yet, so I put it up on my own account.
2. We are looking for ways to contribute back to the community and we feel that this is a good candidate for that. Obviously we are taking the first steps in the open source world here but I really believe we have something to offer here.
3. Provide some form of continuity for the project in case I'm no longer around (for whatever reason) which should hopefully increase possible use of this package as well.

Of course the transfer of ownership is going to change somethings, but I'm confident that a lot will stay the same. We won't be changing the license for example and we would like @ErikEJ to stay on as an outside contributor to this project. For security reasons we are requiring two-factor authentication on the GitHub Organization which is a no-brainer IMHO. We will be adding a code of conduct (based on the Contributor Covenant) and a contribution guide as well.

I'm planning to do the transfer sometime next week, unless of course someone is going to comment on this issue saying I'm making a huge mistake. This is both exciting and a little scary to be honest, but I'm confident that this will help push this project forward. If anybody does have concerns about this, don't hesitate to comment on this issue. I'll leave this open until the move has actually happened.

add topic hacktoberfest

Could we get the topic/tag hacktoberfest added to this repository so that pull requests merged during october get credited and attention drawn to this repository.

See https://github.com/topics/hacktoberfest and https://hacktoberfest.digitalocean.com/faq

Consider adding templates

We might want to add a separate NuGet package to this project containing templates for dotnet new so that new projects can be easily created. Perhaps we can even add templates for specific SQL object types like stored procedures, tables, views, etc.

add support for .refactorlog

I'm migrating our builds to linux based container builds.
I tried out to add an additional project as mentioned in this blog.
Comparing the output, I noticed that the refactor.xml is missing in the dacpac, which is based on the [ProjectName].refactorlog file.

Is this already supported? Do I have to reference the refactorlog file in the project file somehow like the Pre- and post deployment scripts?

Proposal: Adding support for deploying

I've been considering adding support for deploying a project that uses this SDK to a SQL Server as part of a dotnet publish. I have experimented with this and it is fairly easy to extend the publishing pipeline by adding a Publish target to the Sdk.targets file and do something from there. But before I go and implement this I was wondering if this would be considered useful for other folks using this package.

I do see a couple of challenges. First of all, we will need to have sqlpackage.exe to do the actual publishing. On Windows you get this as part of the SSDT tools, but on Linux and Mac there isn't an easy way to acquire it. You would need to download it and then make it available on the path for this package to use it. Or we could include it in this package, but I'm not sure if we're even allowed to redistribute it. Ideally sqlpackage.exe would be available as a .NET Core Global Tool so we could run dotnet tool install SqlPackage but that is not how it has been implemented today. Maybe @dzsquared knows if this is changing at some point?

Of course we'd also need to know the server and database name to deploy to. We could probably use localhost and the name of the project as sane defaults, but allow these to be overridden through properties in the project file.

So basically I'm looking for some feedback on this idea. @ErikEJ What do you think?

PackageReferences don't resolve wildcard versions

When using the <PackageReference> element to build a composite database project, the referenced version should support the use of "floating versions", i.e. wildcards, per the documentation here. When using this SDK (and adding a few debugging messages to see what's going on behind the scenes), it appears that it is not resolving the wildcard to the most recent version, but just using the wildcard literally.

The following is a simplified example of two .csproj files I'm building into a composite database:

Base.csproj

<Project Sdk="MSBuild.Sdk.SqlProj/1.1.1">

  <!-- Database configuration properties -->
  <PropertyGroup>
    <!-- ... -->
  </PropertyGroup>

  <!-- File includes / excludes -->
  <!-- MSBuild.Sdk.SqlProj Sdk defaults to including all *.sql files -->
  <ItemGroup>
    <Content Remove="target/**/*.sql" />
  </ItemGroup>

Plus.csproj

<Project Sdk="MSBuild.Sdk.SqlProj/1.1.1">

  <!-- Database configuration properties -->
  <PropertyGroup>
    <!-- ... -->
  </PropertyGroup>

  <!-- File includes / excludes -->
  <!-- MSBuild.Sdk.SqlProj Sdk defaults to including all *.sql files -->
  <ItemGroup>
    <Content Remove="Scripts/**/*.sql" />
    <Content Remove="target/**/*.sql" />
    <PostDeploy Include="Scripts/Script.PostDeployment.sql" />
    <PreDeploy Include="Scripts/Script.PreDeployment.sql" />
  </ItemGroup>
  <!-- References to dacpac dependencies -->
  <ItemGroup>
    <PackageReference Include="Base" Version="7.6.*" />
  </ItemGroup>
</Project>

When I try to build the "Plus" package, I see the following messages from Sdk.targets:

  Determining projects to restore...
  All projects are up-to-date for restore.
  PackageReference.Identity is Base
  Resolved dacpac file from package Base to /users/jrosenberg/.nuget/packages/base/7.6.*/tools/Base.dacpac
  Resolved database package references: 

Interestingly, if I change the dependency version to 7.7.*, MSBuild stops me before even calling this SDK, so MSBuild and NuGet appear to be resolving the dependency version.

~/Plus/Plus.csproj : error NU1102: Unable to find package Base with version (>= 7.7.0)
~/Plus/Plus.csproj : error NU1102:   - Found 4 version(s) in local [ Nearest version: 7.6.0 ]
~/Plus/Plus.csproj : error NU1102:   - Found 0 version(s) in nuget.org

I would guess that there must be some way to get the actual resolved dependency version from MSBuild/NuGet, above and beyond %(PackageReference.Version), which appears to return the literal contents of that attribute. I'm trying to do some research into this, but so far I haven't been able to figure out how that's done, so thought I would log this issue in the hopes that one of the maintainers understands this better than I do. 😄

NOTE: The ability to use wildcards for pre-release versions appears to be new, but for released versions it has been a part of MSBuild for quite a while.

Add support for package references to other databases

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

How to deploy on a server with non-standard TCP port (1433).

I've tried different combination but it seems like "dotnet publish" does not support specifying the server port to use for deployment. Is there any specific syntax to use?

What I tried:

/p:TargetServerName="localhost,5000"
/p:TargetServerName="localhost:5000"
/p:TargetServerName="(localhost,5000)"

All returns:

MSBUILD : error MSB1006: Property is not valid.
Switch: 1433

Also I tried looking for a "TargetServerPort" in the project source code to no avail.

missing references to build in table sys

while trying to implement tsqlt sql classes I'm getting missing references to the sys schema. e.g. [sys].[extended_properties].

ModelValidationError 
error SQL71501: SqlView: [tSQLt].[TestClasses] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [sys].[extended_properties].[ep]::[name], [sys].[extended_properties].[name] or [sys].[schemas].[ep]::[name].

Pass SQLCMD variable with dotnet publish

I believe it’s possible to pass the value of a SQLCMD variable with dotnet publish. What’s the syntax?

Pass SQLCMD variables at build time

Is it possible to reference objects defined in a referenced package? In a view for example.

I can't see how to specify the DatabaseSqlCmdVariable or an equivalent, like when adding a project reference to a sqlproj, but I'm also not sure if the SqlCmd variable is being expanded with the default value anyway.

Without this the build fails with validation errors e.g.

1>Target CoreCompile:
1> Using package name Database4 and version 1.0.0
1> Using SQL Server version Sql150
1> Adding reference to {}\.nuget\packages\database3\1.0.0\tools\Database3.dacpac
1> Adding SqlCmd variable Database3
1> Adding {}\Database4\View2.sql to the model
1> EXEC : error SQL71561: Error validating element [dbo].[View2]: SqlView: [dbo].[View2] has an unresolved reference to object [$(Database3)].[dbo].[Table2].[Id].
1> EXEC : error SQL71561: Error validating element [dbo].[View2]: SqlView: [dbo].[View2] has an unresolved reference to object [$(Database3)].[dbo].[Table2].
1> EXEC : error SQL71561: Error validating element [dbo].[View2].[Id]: SqlComputedColumn: [dbo].[View2].[Id] has an unresolved reference to object [$(Database3)].[dbo].[Table2].[Id].
1> Found 3 error(s), skip building package

Reference Another DACPAC Directly

I inherited a database that depends on another database. Is it possible to directly reference the DACPAC of another database (without a NuGet package)?

Max number of Input file names

Hi!
trying to implement the solution ( over 2k sql table and sprocs) to our sql project, I got the following message

Option 'Argument: table.sql' expects no more than 255 arguments, but 463 were provided.

Looking at the code I couldn't find a way to increase this limit, but it seems that it's probably defined in the dotnet commandline dependency .

Do you know if there is an easy way to change this limit?

Dry run/ logs on changes made by publishes

dotnet publish [my_db.sqlproj] works well in local development but I'm a little wary of pointing it at our production environment, mostly because there seems to be no method of either previewing the changes that the publish command will make nor is there no log of the changes made.
Is there an option I can configure to get access to this functionality? If not, what would be required to implement such functionality? Depending on the volume/ complexity of work, it's something I might be able to take on.

P.s. I'd like to thank you guys for putting this project out here- it's so far solved a big pain point in an ongoing .net core migration.

Issue for users on Windows with spaces in their username

We've internally identified an issue with MSBuild.Sdk.SqlProj that would cause a build to fail when using this package on Windows and you're username has a space in it. The build would fail with the following error:

You intended to execute a .NET Core program, but dotnet-C:\Users\John does not exist.

This was caused by a bug in the Sdk.targets file that invokes the DacpacTool from the SDK's installation folder where the path wasn't being properly quoted, causing the build to fail. A fix has already been implemented and will be released soon as 1.5.1.

Thanks to @marktaling for reporting this.

Hacktoberfest

This is just an announcement that we are accepting PR's in light of Hacktoberfest. If you want to get involved with this project, the whole month of October is a good time to get started. Have a look through our issues and let us know if you're willing to work on it.

Do make sure you read through our contribution guide as well as our code of conduct before submitting a PR. We will be reviewing all PR's before they are merged.