Search code examples
sql-servergitdeploymentgit-submodulessql-server-data-tools

Submodule file links in SSDT predeploy or postdeploy script producing a 72001 error

In a SQL Server Data Tools (SSDT) project in Visual Studio, we have a "core" set of SQL objects that are included in each SQL project we do - kind of like a class library. We keep these "core" SQL objects in a separate Git repo, and then include them in other projects as a Git submodule.

Once the "core" submodule is linked to the main project, we include the submodule files in our .SQLPROJ file like so:

<Content Include="..\CoreSubmodule\ProjectFolder\Scripts\**\*.*"> <Link>Scripts\%(RecursiveDir)%(FileName)%(Extension)</Link> <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory> </Content>

This works great for regular .sql files in project - they show up with a special icon in Visual Studio indicating that it's a referenced file, and the build engine is able to resolve references/dependencies just fine. Where we're running into a snag, though, is with our Pre- and Post-Deployment scripts.

We have a series of "core" Pre- and Post-Deployment master scripts that are common among projects, which we've just introduced into our "core" submodule. Here's how the directory structure looks at a high-level:

/Scripts/ /PostDeploy/ _PostDeployMaster.sql /ReferenceData/ ReferenceDataScript1.sql

In the above structure:

  • _PostDeploymentMaster.sql is a local file in the project, and is set to Build Action = "PostDeploy". It has a reference to the *ReferenceDataScript1.sql file
  • ReferenceDataScript1.sql is a reference to a file that physically exists in the submodule directory (one level up from our project), and is set to Build Action = "None"
    • Note that Visual Studio displays it in the /ReferenceData/ folder as a linked file

The _PostDeploymentMaster script references other sub-scripts via a SQLCMD reference:

:r .\ReferenceData\ReferenceDataScript1.sql go

Trying to build the project in this manner produces a SQL72001 error in Visual Studio ("The included file does not exist"). Obviously if we physically place the ReferenceDataScript1.sql file in the directory (without having a reference), it builds just fine.

Options we've explored include having a non-Build "buffer" script between the PostDeploy master and the core subscripts (same error), and having pre and post build actions set to physically copy files back and forth from the submodule to the project to satisfy the build engine (a little too hacky for our taste).

Has anyone run into this issue, or have a serviceable workaround?

Solution

  • We wound up working around this issue by using Peter Schott's suggested fix in the original question's comments - using relative paths back to the submodule on disk instead of the "virtual" link inside of the actual Visual Studio SQL project.