I’ve created the following step template using PowerShell to execute SQL files from a particular shared path. The step runs and says completed successfully but does not edit the DB. Neither do I get any of the messages. What could I be doing wrong?
STEP
$ServerName = $OctopusParameters[‘DatabaseServerName’]
$DatabaseName = $OctopusParameters[‘DevelopmentDatabaseName’]
$OutputToFile = $OctopusParameters[‘DBScriptPath’]
$ScriptPath = $OctopusParameters[‘DBScriptPath’]
$SqlLogin = $OctopusParameters[‘DatabaseUserName’]
$SqlPassword = $OctopusParameters[‘DatabasePassword’]
function ExecuteSQLScripts($ScriptPath, $ServerName, $DatabaseName, $SqlLogin, $SqlPassword, [switch]$OutputToFile) {
Write-Host "Executing SQL Scripts…"
Write-Host "- SQL Path: $ScriptPath"
foreach ($f in Get-ChildItem -path $ScriptPath -Filter *.sql | Sort-Object) {
Write-Host "- Script: $($f.FullName)"
if ($OutputToFile) {
$out = “$ScriptPath” + $f.BaseName + ".txt"
Write-Host "- Output: $out"
invoke-sqlcmd -S $ServerName -U $Username -P $Password -b -I -i $f.FullName -o $out
}
else {
invoke-sqlcmd -S $ServerName -U $Username -P $Password -b -I -i $f.FullName
}
if ($LastExitCode -ne 0) {
throw “SQL Script Execution failed. sqlcmd.exe returned exit code $LastExitCode.”
}
}
}
PARAMETERS
Server Name
#{$ServerName}
Database Name
#{$DatabaseName}
Output File
#{$OutputToFile}
Path to SQL scripts
#{$ScriptPath}
Sql Login
#{$SqlLogin}
Sql Password
#{$SqlPassword}
Anyway I didn’t see the attachment, but find below the json. I’m pretty sure it was the one.