How do you reference a sql script (not procedure) in a Script.PostDeployment.sql in Azure DevOps?

user_1187583 0
Viewed 1047

How to get the path to a script (not procedure) in Azure DevOps build?

I'm trying to get the path to either my solution or project file to use in Visual Studio database project PostDeployment.

Works locally

In the Script.PostDeployment.sql file in a Visual Studio Database project I have the following code

SELECT @solutionDir = REPLACE('$(SolutionPath)','MySoulution.sln','');
SET @File = @solutionDir + 'myScript.sql'
-- and here I can use the @File

When I do a local publish/or build I can use the variable/macro $(SolutionPath) where I get the full local path to the solution.

With that I can point to the script I need to access.

Fails in AzureDevops

But Azure DevOps build doesn´t have the $(SolutionPath) macro and I get the following error

SCRIPT.POSTDEPLOYMENT.SQL(17,32,17,32): Build error SQL72008: Variable SolutionPath is not defined.

So I need a way (in AzureDevops) in the build step to get the path to the scripts.

What I have tried

  1. I have tried all kinds of macros that just don't seem to work.
  2. I have tried to work with predefined variables
  3. I tried to use SqlCommandVariableOverride but it seems to have gone missing from the database project some time ago.

Questions

  1. How can I add $(SolutionPath) (or something else) to my build step so this works both locally and in Azure DevOps build?
  2. Is there another way to get a solution or project url to the postdeployment SQLCMD file?
  3. Is MSBuild Extension Pack something I should look at? I will if it is my only option.

p.s

I also created a ticket for this in Azure Pipelines Tasks since they don´t seem to be that active in answering and I can't wait.

e.s

I asked the same question at developercommunity.visualstudio.com. Lets hope somebody there will be able to answer the question. If so I will update this question with an answer.

database-project sqlcmd azure-devops
1 Answers

So first double check that your build is publishing the artifacts. there should be a blue button in the top right corner of your build that says "artifacts". To have access to the artifact you need to do an archive task after you do the build and point it at 

$(Build.ArtifactStagingDirectory)

Then I would recommend not using SQL to make direct references to the filesystem for the build server. If this is absolutely necessary then you should use environment variables.

I would recommend setting up a release pipeline for deployment of the SQL script. Release pipelines are really designed for deployment, whereas builds are for compilation.

To configure the release, point it to the artifact and then you can access that artifact by $(System.DefaultWorkingDirectory)/**/*.zip then run whatever commands you want to do to process that.

user_36725060
Related