SQL Server database project in VS. Order of sql script generation step in build process

Question

I have a SQL Server database project in my solution and several dependencies projects.

When I compiling (in VS) this database project I've get a SQL script and a .dacpac file as result.

But also I want to aggregate all my dependencies projects in one dll and make SQL script/.dacpac file only for this result dll.

I'm using ILMerge.MSBuild.Tasks.ILMerge to aggregate all dll on AfterBuild event in sqlproj. But this aggregation happens after generating SQL script.

How can I enforce SQL script generation in the end?

Build log:

2>------ Rebuild All started: Project: TestCLR, Configuration: Debug Any CPU ------
2>      D:\Program Files (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin\Roslyn\csc.exe /noconfig /nowarn:1701,1702,2008 /fullpaths /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva+ /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\mscorlib.dll" /reference:D:\Work\FF\Sql-Objects\Tools\TestCLR\bin\Debug\TestLogic.dll /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\System.Data.dll" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\System.dll" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\System.Xml.dll" /debug+ /debug:full /optimize- /out:obj\Debug\TestCLR.dll /subsystemversion:6.00 /target:library /warnaserror- /utf8output /langversion:7.3 TestProcedures.cs 
2>      Loading project references...
2>      Loading project files...
2>      Building the project model and resolving object interdependencies...
2>      Validating the project model...
2>      Writing model to D:\Work\FF\Sql-Objects\Tools\TestCLR\obj\Debug\Model.xml...
2>      Writing create script to TestCLR_Create.sql...
2>      TestCLR -> D:\Work\FF\Sql-Objects\Tools\TestCLR\bin\Debug\TestCLR.dll
2>      TestCLR -> D:\Work\FF\Sql-Objects\Tools\TestCLR\bin\Debug\TestCLR.dacpac
2>      ILMerge bin\Debug\TestCLR.dll;bin\Debug\TestLogic.dll;bin\Debug\Sider.dll -> D:\Work\FF\Sql-Objects\Tools\TestCLR\bin\Debug\TestCLR.dll
========== Rebuild All: 2 succeeded, 0 failed, 0 skipped ==========

.sqlproj part with ILMerge (Right now it's on the last place. Right before /Project tag )

  <UsingTask TaskName="ILMerge.MSBuild.Tasks.ILMerge" AssemblyFile="$(SolutionDir)\packages\ILMerge.MSBuild.Tasks.1.0.0.3\tools\ILMerge.MSBuild.Tasks.dll" />
  <Target Name="AfterBuild">
    <ItemGroup>
      <MergeAsm Include="bin\Debug\*.dll" />
    </ItemGroup>
    <PropertyGroup>
      <MergedAssembly>$(ProjectDir)$(OutDir)RedisCLR.dll</MergedAssembly>
    </PropertyGroup>
    <Message Text="ILMerge @(MergeAsm) -&gt; $(MergedAssembly)" Importance="high" />
    <ILMerge InputAssemblies="@(MergeAsm)" OutputFile="$(MergedAssembly)" TargetKind="SameAsPrimaryAssembly" />
  </Target>

Much appreciate Mr. Solomon Rutzky. This solution was wery helpful.

<PropertyGroup>
  <SqlBuildDependsOn>
    BeforeSqlBuild;
    $(SqlBuildDependsOn);
  </SqlBuildDependsOn>
</PropertyGroup>
<Target Name="BeforeSqlBuild">
  ILMerge things
</Target>

But there was one curious thing. Dll comes to Bin\debug in the end of flow (I think on PostBuildEvent step or near). And if I move ILMerge upper on the flow - it returns error, that there are no DLLs. Changed config to gater dll from obj folder and from dependant project.

Solomon Rutzky · Accepted Answer · 2021-06-23 15:21:12Z

1

Interesting question. I don't know of any easy way to do this, or if it's even possible in the first place. However, there are two options that I know of for altering the build process:

Option 1

Inject a build step / target. SSDT is missing at least two build steps/targets — "BeforeSqlBuild" and "BeforePublish" — because the "AfterBuild" target is too late in the process (as you have discovered).

To get around the "CLR strict security" debacle introduced in SQL Server 2017 (documented in the following post of mine: SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2), I updated the .sqlproj file by placing the following at the end, just before the closing </Project> tag (to sign the assembly with a certificate):

<PropertyGroup>
  <SqlBuildDependsOn>
    BeforeSqlBuild;
    $(SqlBuildDependsOn);
  </SqlBuildDependsOn>
</PropertyGroup>
<Target Name="BeforeSqlBuild">
  <Exec Command="{DOS / Windows commands}"/>
</Target>

You can keep the <Exec Command="..."/> and/or add the contents of your existing "AfterBuild" target. HOWEVER, even with this performing the action at the correct time, I'm not entirely certain that you will be able to change the assembly name that it will want to serialize and put in the "Create" script (i.e. switch from using "TestCLR.dll" to "RedisCLR.dll"). You can try re-assigning the build variable, but I've never tried that and am not sure it's allowed. In which case the next option might help.

(Please vote for my enhancement request to improve the SSDT build process:
Add MSBuild predefined Targets for "BeforeSqlBuild" and "BeforePublish" to SSDT SQL Server Database Projects)

Option 2

Use .NET to create a custom build task that has access to the objects, files, and overall process:

answered Jun 23, 2021 at 15:21

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Viktor Bardakov Over a year ago

Much appreciate with that Option 1! It works for me. But there was one curious thing. Dll comes to Bin\debug in the end of flow (I think on PostBuildEvent step or near). And if I move ILMerge upper on the flow - it returns error, that there are no DLLs. Changed config to gater dll from obj folder and from dependant project.

Solomon Rutzky Over a year ago

@ViktorBardakov Glad it worked! I was a little worried due to the DLL name change. How did you end up handling that part? Also, sorry I forgot to mention that you need to look in the obj folder. I had documented that in my blog post but missed the significance of it being different than where you had been getting the DLLs from.

Viktor Bardakov Over a year ago

My mistake. When I change "Redis" to "Test" forgot about MergedAssembly. In fact I din't change DLL name. Output for ILmerge (MergedAssembly property) is the same name, that was generated in sqlproj. But I placed result dll in OBJ folder, so in the end build can take it from there and paste in bin folder @SolomonRutzky

Collectives™ on Stack Overflow

SQL Server database project in VS. Order of sql script generation step in build process

1 Answer 1

Option 1

Option 2

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1

Option 2

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related