What are the challenges you face when working across database platforms? Take the survey

Issue creating Release Artifacts using Powershell cmdlets

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

Best Answer


  • Options
    DiogoDiogo Posts: 67 Silver 5
    edited August 13, 2018 8:11AM
    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?
  • Options
    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

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


    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.

  • Options
    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.
Sign In or Register to comment.