New-DatabaseReleaseArtifact doesn't support SQL Change Automation Build Artifacts as a target

RomanPekar

I'm trying to adjust my pipeline for DB builds and want to use powershell cmdlets for that.

To speed up the deployment I want to build a diff deployment package and I'm providing a target 'baseline' package to New-DatabaseReleaseArtifact. As said in the documentation - one of the possible values for -Target parameter is a Database Build Artifact object produced by the New-DatabaseBuildArtifact cmdlet. However, when I run the cmdlet with such a parameter I get the following error:
New-DatabaseReleaseArtifact : New release does not support SQL Change Automation Build Artifacts as a target parameter.I also didn't manage to create a release artifact based on the scripts folder (which is also supposed to work according to documentation).

Do I understand correctly that the only possible -Target parameter is valid database connection? Or is it possible to create a release artifact without access to Sql Server?

DanC

Hi @RomanPekar

After reading through the document again, unfortunately this isn't possible as you're using a SQL Change Automation project:

"The Target parameter must be a connection string or a DatabaseConnection when using SQL Change Automation Projects." alternatively "You can also specify a NuGet package as the Target parameter. However, if you do this, static data won't be deployed."

As an example I think you can do something like this:

https://documentation.red-gate.com/sca/reference/powershell-cmdlets/new-databasebuildartifact

DanC

Hi @RomanPekar

You should be able to use any of the following resources for the -Target parameter

- a Database Connection object created by the New-DatabaseConnection cmdlet

- a database connection string

- a path for a NuGet package or .zip file. This must contain a scripts folder located at db\state

- a Database Build Artifact object produced by the New-DatabaseBuildArtifact cmdlet

- a path for a scripts folder, created by SQL Compare or from your SQL Source Control database repository

RomanPekar

Hi @DanC

Yes that's what is written in the documentation. However, when I'm trying to use Database Build Artifact as an example I receive the error which I mentioned in my starting post. Could you please check it out?

Just to be sure, I run a test - I've created an empty SCA project, created a build artifact out of it and then tries to use it as a target parameter for release artifact. I still get the error:



Could you give me a simple reproducible example on how do I use such a parameter? I might be missing something

DanC

Hi @RomanPekar

I just tested this myself and from what I can summarize it that the New-DatabaseReleaseArtifact parameter cannot have the same source for both Source and Target 

I'm going to validate this with the developers and get a more meaningful error message or documentation improvement

For a working solution this was my script:

$projectPath = "C:\Users\Dan.Calver\Documents\SQL Change Automation - SCA Projects\NorthWindTest\NorthWindTest.sqlproj"

$target = New-DatabaseConnection -ServerInstance "PS-LT-DANC\DC_SQLSERVER2019" -Database "EmptyDB"

$project = $projectPath | New-DatabaseProjectObject

$buildArtifact = New-DatabaseBuildArtifact $project -PackageId "testsca" -PackageVersion '1'

$releaseArtifact = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $target

RomanPekar

@DanC

Sorry but that doesn't really answer my question.
My initial question was - is it possible to run New-DatabaseReleaseArtifact with -Target parameter being either nuget package or build arfifact object?

Now you're giving me the script which uses connection object instead. I know that it works, but I want to be able to build release artifact without access to Sql Server.

Could you adjust your script in a way that it uses either nuget package or build artifact and show me how it can be done?