Issue creating Release Artifacts using Powershell cmdlets

CopperStarSystems

I'm trying to get a powershell-based CI/CD pipeline configured, but I'm having difficulty with the Powershell cmdlets.  What I'm trying to accomplish is to programmatically deploy an existing SCA database to an empty database.

The issue I'm running into is that when I build the release artifact, it doesn't seem to be picking up anything but the default system database tables, so the generated script looks like this:

/*<br>    Generated on 10/Aug/2018 11:00 by Redgate SQL Change Automation v3.0.1.2275<br>    This script is empty because the Target and Source schemas are identical.<br>*/

I would instead have expected this script to contain CREATE TABLE statements for the tables that exist in the SCA project.

Am I overlooking something somewhere?  The script itself is fairly simple at this point, as it's just in a proof-of-concept phase:

# CI build/deploy of a Redgate database<br>Install-Module -Name SqlServer -Scope CurrentUser<br><br># NuGet<br>$packageVersion = "1.0.0"<br>$packageId = "Redgate.Sca.FromExistingDb"<br>$project = [String]::Format("{0}\{1}", (pwd), "Redgate.Sca.FromExistingDb")<br><br># Artifacts<br>$buildArtifactLocation = [String]::Format("{0}\DbArtifacts\Build", (pwd))<br>$releaseArtifactLocation = [String]::Format("{0}\DbArtifacts\Release", (pwd))<br><br># Deployment<br>$deploymentTargetServer = ".\SQLEXPRESS"<br>$deploymentTargetDb = "Redgate.Sca.CI.Staging"<br><br>Write-Host("Validating database project")<br>$validatedProject = $project | Invoke-DatabaseBuild<br>$documentation = $validatedProject | New-DatabaseDocumentation<br><br>Write-Host("Building database Build artifact...")<br>$buildArtifact = $validatedProject | New-DatabaseBuildArtifact -PackageId $packageId -PackageVersion $packageVersion -Documentation $documentation<br><br>Write-Host("Exporting database Build artifact...")<br>$buildArtifact | Export-DatabaseBuildArtifact -Path $buildArtifactLocation<br><br>Write-Host("Building database Release artifact")<br>$deploymentConnection = New-DatabaseConnection -ServerInstance $deploymentTargetServer -Database $deploymentTargetDb<br>$buildArtifact = Import-DatabaseBuildArtifact ([String]::Format("{0}\{1}.{2}.nupkg", $buildArtifactLocation, $packageId, $packageVersion))<br>$releaseArtifact = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $deploymentConnection -IncludeIdenticalsInReport -verbose<br><br>Write-Host("Exporting database Release artifact")<br>$releaseArtifact | Export-DatabaseReleaseArtifact -Path $releaseArtifactLocation

Any guidance would be greatly appreciated

Diogo

My suggestion for the sql proj was based on the cmdlet reference. https://documentation.red-gate.com/display/SCA3/Invoke-DatabaseBuild
Notice that when we pass in a folder we're referring to a SQL Source Control project not a SCA project which I think is what you are using.

Diogo

Hi @CopperStarSystems,

I think you should using the path to your  SCA project file (a .sqlproj file) on your project variable. Does that make a difference?

CopperStarSystems

Hi @Diogo do you mean that I should include the .sqlproj filename in my $project variable?  Basically, the way this is laid out on the filesystem is:

- Solution root folder

  - [powershell script]

  - Redgate.Sca.FromExistingDb [folder for SCA project]

  - DbArtifacts [root folder for build/release artifacts]

    - Build

    - Release

...etc...

So normally, when I execute the Powershell script from the root folder, $project resolves to something like:

c:\path\to\solution\root\Redgate.Sca.FromExistingDb

I'm asking because the examples I've seen all seem to point to the folder containing the .sqlproj rather than the .sqlproj file itself.

Any clarification you can provide will be greatly appreciated.

PS:  The script does successfully build the .sqlproj Build artifacts, I can open the .nuget package and see my migration scripts, etc.  The issue is that the Release artifact indicates that there are no changes to be made to the target database even though the source and target aren't in sync.

CopperStarSystems

Hi @Diogo Thanks for that last hint, I had overlooked the distinction between passing a folder (for Sql Source Control) vs. passing a .sqlproj for SCA.