PackageScript.sql - questions: ReleaseVersion and PackageVersion and commented sqlcmd vars

PeterDaniels

This is a follow-up to my previous post: https://forum.red-gate.com/discussion/84832/is-there-a-project-file-setting-to-remove-the-server-name-check#latest

In build artifacts (nupkg archives), I'm now able to see the PackageScript.sql in the \db\output folder.  I have a few questions regarding this file.  Please note that these questions are based on the fact that I currently have to pass a SQL script to ops DBAs that they will exec in SSMS.

First, how come the PackageVersion is set to "(undefined)" in the script?  We have to pass PackageVersion to New-DatabaseBuildArtifact.  Why wouldn't it set that var in the script?

Second, I don't see a way to pass any SqlCmdVariables to New-DatabaseBuildArtifact or Export-DatabaseBuildArtifact.  So, I would have to edit the file to set this sqlcmd var.   

Third, the sqlcmd vars are commented out.  Is there a parm to set to uncomment those?

Lastly, if I exec  this script the Version column is not set in the __MigrationLog.

Seems like I will have to manually (probaby via powershell) extract and process the PackageScript.sql to uncomment the SQLCMD setvar statements, set the ReleaseVersion and PackageVersion.

Any other options?

TIA,

-Peter

Monday

PackageVersion is for the version of the Nuget package. In my process I publish the package to a Nuget feed and other tools will know when to pick it up based on that version. I don't think PackageVersion applies to you since you are manually handing the script to your Ops.

Redgate should probably address the other questions. 

On a side note it would be nice to see Redgate take a little time in making some guides, documentation, strategy , videos, etc for people using SCA like this. I think this is and will be a common approach to introduce the tool in baby steps while building up confidence or waiting for resources to free up to implement. A lot of the documentation and marketing is geared towards CI/CD and that's a great goal but in the real world a lot of us need to take a phased , agile approach.

Eddie D

Hi, thank you for your forum post.

The Package Version parameters that are passed are used in the title of the package.

This help document explains the process for passing SQL CMD variables to the process to be used in the package deployment: https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-change-automation-core/sqlcmd-package-deployment 

Many thanks
Eddie

PeterDaniels

I guess it makes sense that package version might not be set since I can actually get the PackageScript.sql without calling Export-DatabaseBuildArtifact.

Re: ReleaseVersion, I understand that I could set it in the project on the SQLCMD vars tab, but I want it to be dynamically set via powershell. Since Invoke-DatabaseBuild doesn't have a SqlCmdVariables param like New-DatabaseReleaseArtifact does, I don't see a programmatic way to set release version other than processing the file, which I have to do anyway side the SQLCMD setvar section is commented out anyhoo.

PeterDaniels

@Eddie D and @Monday , thanks for the replies. Eddie, I am aware of the docs you suggested. That still requires the ops DBA to use powershell to deploy, so it's not an option until I socialize that possibility.  Currently, I'm trying to automate as much as possible before delivering them a sql file. I would like to see an option to set various SQLCMD vars in the packagescript.sql when I call invoke-databaseBuild.

PeterDaniels

One more comment for RG folks: I would still expect the PackageVersion SQLCMD var to be populated in the PackageScript.sql after I call Export-DatabaseBuildArtifact, since we are required to call that cmdlet with a  PackageVersion parameter.