SQLCompare options in Change automation cmdlet behavior with dependencies

BenT

Hello,

I'm using SQL Change Automation cmdlets to deploy script changes with SQL Compare Options to include dependencies. But sounds like when the changes script is generated, the script changes order is not taking into account dependencies.
I need to deploy a table change before a stored procedure, but into the script the stored procedure modifications are applied before table modifications which is causing deployment failure.
Is there any solution for that case?

Thanks in advance for you support,

Regards,

BenT

DanC

Hi @BenT

Could you please confirm whether you're using a SQL Change Automation project or SQL Source Control project?

BenT

Hi DanC,

I'm using a SQL Source Control project, comparing the repo on which we are synchronizing our developments with a target database

Thanks for your support,

Regards,

BenT

DanC

Hi @BenT

Thank you for confirming so promptly!

Are you using the PowerShell directly or any of the Add-ons with the cmdlets for your deployment?

BenT

Hi Dan,

I created PowerShell scripts for the deployments using SQL Change Automation cmdlets.

Regards,

BenT

DanC

Hi @BenT

Would you mind sharing those with me to review the code?

If you'd prefer not to share them publicly, I can reach out via a support ticket

BenT

Hi Dan,
I think there is nothing confidential in the code I already adapted from a Red-Gate employee post.
Please find the code below:

Database build from Source Control db folder synchronized:
--------------------------------------------------------------------------------

<# -------Create a build artifact from source control --------#>

[String]$path ="$(Build.SourcesDirectory)\DataBases\EDW"

[String]$packageVersion = "$(Build.BuildId)"

[String]$packageID = "EDW_pkg"   

[String]$buildArtifactPath = "$(Build.ArtifactStagingDirectory)"

   

$path |

  New-DatabaseProjectObject | #wrap up the script and create a build artefact

   New-DatabaseBuildArtifact -PackageId $packageID -PackageVersion $packageVersion `

     -PackageDescription 'EDW scripts' | # and save it as a file

        Export-DatabaseBuildArtifact `

          -Path $buildArtifactPath


Database deployment:
-------------------------------

<# -------Create a release artefact from a build package --------#>

[String]$currentEnv =[System.Environment]::GetEnvironmentVariable('RELEASE_ENVIRONMENTNAME')

[String]$deployDataBase_YesNo ="$(DeployDatabase_Y_N)"

if ( $currentEnv -ne 'Development' )  #do no deploy to dev DB EDW, already synchronized

{

if ( $deployDataBase_YesNo.ToUpper() -eq 'Y' ) #deploy when user put "Y" (Yes) in variable

{

[String]$packageVersion = "$(Build.BuildId)"   

[String]$buildArtifactPath = "$(System.ArtifactsDirectory)\$(Release.PrimaryArtifactSourceAlias)\drop\EDW_pkg.$packageVersion.nupkg"

[String]$targeServerDB = "$(SQLConnectionString)$(SQLDataBase)"

[String]$ReleasesPath = "$(System.ArtifactsDirectory)\$(Release.PrimaryArtifactSourceAlias)\drop\DB_Releases\EDW\$(ReleaseFolder)\$(Release.ReleaseName)"

[String]$BuildReportPath = "C:\BusinessIntelligence\ExportReleases\$(Release.DefinitionName)\$(Release.ReleaseId)_$(Release.ReleaseName)\$(ReleaseFolder)\BuildReport.html"

[String]$options = "IncludeDependencies,NoTransactions"

$iReleaseArtifact=New-DatabaseReleaseArtifact   `

-Source $buildArtifactPath    `

-Target $targeServerDB    `

-SQLCompareOptions $options

 

Use-DatabaseReleaseArtifact $iReleaseArtifact    `

-DeployTo $targeServerDB   `

-SkipPostUpdateSchemaCheck   `

-QueryBatchTimeout 900

$iReleaseArtifact.ReportHtml>$BuildReportPath

start $BuildReportPath

}

}



Regards,

Ben

DanC

Hi @BenT

In this case IncludeDependencies is the default behavior of the SCA PS modules and shouldn't need to be included.

In this case, I'm going to reach out via a support ticket as I'll need your Build Artifact and a copy of the database

BenT

Hi Dan,

Ok thanks let's do it this way so.
Waiting for the support ticket to be created.

Regards

Ben