Problem Generating Html Report Using DbUp during Octopus Deployment

Question

Using Octopus Deploy to deploy a simple API. The first step of our deployment process is to generate an HTML report with the delta of the scripts run vs the scripts required to run. I used this tutorial to create the step.

The relevant code in my console application is:

var reportLocationSection = appConfiguration.GetSection(previewReportCmdLineFlag);

if (reportLocationSection.Value is not null)
{
    // Generate a preview file so Octopus Deploy can generate an artifact for approvals
    try
    {
        var report = reportLocationSection.Value;

        var fullReportPath = Path.Combine(report, deltaReportName);

        Console.WriteLine($"Generating upgrade report at {fullReportPath}");

        upgrader.GenerateUpgradeHtmlReport(fullReportPath);

    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        return operationError;
    }
}

The Powershell which I am using in the script step is:

# Get the extracted path for the package
$packagePath = $OctopusParameters["Octopus.Action.Package[DatabaseUpdater].ExtractedPath"]
$connectionString = $OctopusParameters["Project.Database.ConnectionString"]
$reportPath = $OctopusParameters["Project.HtmlReport.Location"]

Write-Host "Report Path: $($reportPath)"

$exeToRun = "$($packagePath)\DatabaseUpdater.exe"
$generatedReport = "$($reportPath)\UpgradeReport.html"

Write-Host "Generated Report: $($generatedReport)"

if ((test-path $reportPath) -eq $false){    
    New-Item "Creating new directory..."
} else {
    New-Item "Directory already exists."
}

# Run this .NET app, passing in the Connection String and a flag
# which tells the app to create a report, but not update the database
& $exeToRun --connectionString="$($connectionString)" --previewReportPath="$($reportPath)"

New-OctopusArtifact -Path "$($generatedReport)"

The error reported by Octopus is:
'Could not find file 'C:\DeltaReports\Some API\2.9.15-DbUp-Test-9\UpgradeReport.html'.'

I'm guessing that is being thrown when this powershell line is hit: New-OctopusArtifact ... And that seems to indicate that the report was never created.

I've used a bit of logging to log out certain variables and the values look sound:

• Report Path: C:\DeltaReports\Some API\2.9.15-DbUp-Test-9
• Generated Report: C:\DeltaReports\Some API\2.9.15-DbUp-Test-9\UpgradeReport.html
• Generating upgrade report at C:\DeltaReports\Some API\2.9.15-DbUp-Test-9\UpgradeReport.html

As you can see in the C#, the relevant code is wrapped in a try/catch block, but I'm not sure whether the error is being written out there or at a later point by Octopus (I'd need to do a pull request to add a marker in the code).

Can anyone see a way forward win resolving this? Has anyone else encountered this?

Cheers

Answer 1

I recently redid some of the work from that article for this video up on YouTube. I did run into some issues with the .SQL files not being included in the assembly. I think it was after I upgraded to .NET 6. But that might be a coincidence.

Anyway, because the files weren't being included in the assembly, when I ran the command line app via Octopus, it wouldn't properly generate the file for me. I ended up configuring the project to copy the .SQL files to a folder in the output directory instead of embedding them in the assembly. You can view a sample package here.

One thing that helped me is running the app in a debugger with the same parameters just to make sure it was actually generating the file. I'm sure you already thought of that, but I'd be remiss if I forgot to include it in my answer. :)

FWIW, this is my updated scripts.

First, the Octopus Script:

$packagePath = $OctopusParameters["Octopus.Action.Package[Trident.Database].ExtractedPath"]
$connectionString = $OctopusParameters["Project.Connection.String"]
$environmentName = $OctopusParameters["Octopus.Environment.Name"]
$reportPath = $OctopusParameters["Project.Database.Report.Path"]

cd $packagePath
$appToRun = ".\Octopus.Trident.Database.DbUp"
$generatedReport = "$reportPath\UpgradeReport.html"

& $appToRun --ConnectionString="$connectionString" --PreviewReportPath="$reportPath"

New-OctopusArtifact -Path "$generatedReport" -Name "$environmentName.UpgradeReport.html"

My C# code can be found here but for ease of use, you can see it all here (I'm not proud of how I parse the parameters).

        static void Main(string[] args)
        {
            var connectionString = args.FirstOrDefault(x => x.StartsWith("--ConnectionString", StringComparison.OrdinalIgnoreCase));

            connectionString = connectionString.Substring(connectionString.IndexOf("=") + 1).Replace(@"""", string.Empty);

            var executingPath = Assembly.GetExecutingAssembly().Location.Replace("Octopus.Trident.Database.DbUp", "").Replace(".dll", "").Replace(".exe", "");
            Console.WriteLine($"The execution location is {executingPath}");

            var deploymentScriptPath = Path.Combine(executingPath, "DeploymentScripts");
            Console.WriteLine($"The deployment script path is located at {deploymentScriptPath}");

            var postDeploymentScriptsPath = Path.Combine(executingPath, "PostDeploymentScripts");
            Console.WriteLine($"The deployment script path is located at {postDeploymentScriptsPath}");

            var upgradeEngineBuilder = DeployChanges.To
                .SqlDatabase(connectionString, null)                
                .WithScriptsFromFileSystem(deploymentScriptPath, new SqlScriptOptions { ScriptType = ScriptType.RunOnce, RunGroupOrder = 1 })
                .WithScriptsFromFileSystem(postDeploymentScriptsPath, new SqlScriptOptions { ScriptType = ScriptType.RunAlways, RunGroupOrder = 2 })
                .WithTransactionPerScript()
                .LogToConsole();

            var upgrader = upgradeEngineBuilder.Build();

            Console.WriteLine("Is upgrade required: " + upgrader.IsUpgradeRequired());
            
            if (args.Any(a => a.StartsWith("--PreviewReportPath", StringComparison.InvariantCultureIgnoreCase)))
            {
                // Generate a preview file so Octopus Deploy can generate an artifact for approvals
                var report = args.FirstOrDefault(x => x.StartsWith("--PreviewReportPath", StringComparison.OrdinalIgnoreCase));
                report = report.Substring(report.IndexOf("=") + 1).Replace(@"""", string.Empty);

                if (Directory.Exists(report) == false)
                {
                    Directory.CreateDirectory(report);
                }

                var fullReportPath = Path.Combine(report, "UpgradeReport.html");

                if (File.Exists(fullReportPath) == true)
                {
                    File.Delete(fullReportPath);
                }

                Console.WriteLine($"Generating the report at {fullReportPath}");
                
                upgrader.GenerateUpgradeHtmlReport(fullReportPath);
            }
            else
            {
                var result = upgrader.PerformUpgrade();

                // Display the result
                if (result.Successful)
                {
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.WriteLine("Success!");
                }
                else
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine(result.Error);
                    Console.WriteLine("Failed!");
                }
            }
        }

I hope that helps!

Answer 2

After long and detailed investigation, we discovered the answer was quite obvious.
We assumed the existing deploy process configuration was sound. Because we never had a problem with it (until now). As it transpires, there was a problem which led to the Development deployments being deployed twice.
Hence, the errors like the one above and others which talked about file handles being held by another process.

It was actually obvious in hindsight, but we were blind to it as we thought the existing process was sound