Create Database Release task fails with New-DatabaseRelease : The specified argument is neither a valid database connection string nor a path to an existing file or folder

Legacy Uncategorised
1

Hi all,

I’m trying to get Octopus up and running in our environment for database deploys. I’ve used the Step Template from the Community Library. The Create Database Release task is the only step right now besides the ‘Deploy a NuGet package’ step that precedes it. The full failure message is:

New-DatabaseConnection, SQL Release 1.2.0.2364, Copyright c Red Gate Software Ltd 2014-2015
Info 16:36:29
In trial: expires 5/13/2015 6:01:45 PM
Test-DatabaseConnection, SQL Release 1.2.0.2364, Copyright c Red Gate Software Ltd 2014-2015
In trial: expires 5/13/2015 6:01:45 PM
New-DatabaseRelease, SQL Release 1.2.0.2364, Copyright c Red Gate Software Ltd 2014-2015
VERBOSE: Calling: C:\Program Files (x86)\Red Gate\SQL Release\SC\SQLCompare.exe
/? /OutputWidth:1024
Error 16:36:29
New-DatabaseRelease : The specified argument is neither a valid database
Error 16:36:29
connection string nor a path to an existing file or folder:
Error 16:36:29
At C:\Windows\system32\config\systemprofile\AppData\Local\Tentacle\Temp\fd4e2c7
Error 16:36:29
b-6a60-4178-b4af-de9b426f5be3.ps1:58 char:20
Error 16:36:29

  •     $release = New-DatabaseRelease -Target $targetDB -Source

Error 16:36:29
$databaseStateP …
Error 16:36:29

Error 16:36:29

Error    16:36:29
    + CategoryInfo          : InvalidArgument: (:) [New-DatabaseRelease], Term 
Error    16:36:29
   inatingException
Error    16:36:29
    + FullyQualifiedErrorId : InvalidConnectionStringOrPathNotFound,RedGate.SQ 
Error    16:36:29
   LRelease.PowerShell.Commands.NewDatabaseReleaseCommand
Info    16:36:29
VERBOSE: C:\Program Files (x86)\Red Gate\SQL Release\SC\SQLCompare.exe ended 
with exit code 0
VERBOSE: Calling: C:\Program Files (x86)\Red Gate\SQL 
Release\SDC\SQLDataCompare.exe /? /OutputWidth:1024
VERBOSE: C:\Program Files (x86)\Red Gate\SQL Release\SDC\SQLDataCompare.exe 
ended with exit code 0
In trial: expires 5/13/2015 6:01:45 PM
Info    16:36:29
==============================================
PowerShell exit code: 1
==============================================
Fatal    16:36:29
PowerShell script returned a non-zero exit code: 1
Tentacle version 2.6.4.951

My variables:

```
Name	Value	Scope
DatabaseName_SQL1_mtx_search	mtx_search	
DatabaseServer_SQL1	SQL1	Production
DatabaseServer_SQL1	QARLSSQL1	QARLS
DatabaseServer_SQL1	QARSQL1	QAR
DatabaseServer_SQL1	LAB1SQL1	LAB1
SQLDeploymentResourcesExportPath	C:\Octopus	
SQLServerPassword	●●●●●●●●	Production
SQLServerPassword	●●●●●●●●	LAB1; QAR; QARLS
SQLServerUsername	sa	
```

And my settings for the Create Database Release  step:

```
Export path	 - 	#{SQLDeploymentResourcesExportPath}
Delete files in export folder	 - 	Yes
Database package step	 - 	Download and Extract Nuget Package
Target SQL Server instance	 - 	#{DatabaseServer_SQL1}
Target database name	 - 	#{DatabaseName_SQL1_mtx_search}
Username (optional)	 - 	sa
Password (optional)	 - 	munged
Filter path (optional)	 - 	
SQL Compare options (optional)	 - 	AddDatabaseUseStatement,DecryptPost2KEncryptedObjects,DecryptPost2KEncryptedObjects,ForceColumnOrder,IgnoreConstraintNames,IgnoreFileGroups,IgnoreFillFactor,IgnoreFullTextIndexing,IgnoreReplicationTriggers,IgnoreSquareBrackets,IgnoreStatistics,IgnoretSQLt,IgnoreWhiteSpace,UseClrUdtToStringForClrMigration
Transaction isolation level (optional)	 - 	Serializable
Ignore static data	 - 	No
```

Can you help me resolve this problem?
2

Hi,

I think the problem is that the -Source parameter of the New-DatabaseRelease cmdlet (line 58 of the step template) points to an empty folder. It’s supposed to point to a db\state sub-folder in the location where the Database NuGet package was extracted by the ‘Download and Extract Nuget Package’ step.

To help debug this, could you insert the following line immediately after the declaration of the $databaseStatePath variable on line 28, then rerun the release. It will write the actual path being looked for into the log, which is a good first step to try and track this down further.

Write-Host “DB State path: ‘$databaseStatePath’”

Please note that making a change to the PowerShell and getting your Project to use it is a two step process. First, you must edit the step template in your Library and save the changes. Next you must edit the Create Database Release step in your project and click on the orange Update button that should now have appeared.

Once you’ve rerun this release, have a look at the value of $databaseStatePath in the release log, and see if this matches the location that the database package was extracted on your Tentacle.

3

Hi Chris,

Thanks for getting back to me. Here is the path output in the logs after making the change you suggested:

DB State path: ‘C:\Octopus\Applications\QARLS\sql1_mtx_search\4.47_22\db\state’

On the tentacle, I can navigate to 'C:\Octopus\Applications\QARLS\sql1_mtx_search\4.47_22 and it contains the folders I expect (Tables, Functions, Views, etc.) but no db subfolder. I’m not real sure how that and the state folder are generated.

4

Hi,

When a database NuGet package generated by Redgate’s SQL CI tool is extracted, the database scripts folder (the one containing sub-folders for Tables, Functions, Views, etc) is located in a db\state sub-folder. The step-template PowerShell thus appends db\state to the location of the extracted NuGet package. That happens in lines 22 and 28 of the script:

22: $packagePath = $OctopusParameters[“Octopus.Action[$SQLReleaseNuGetDbPackageDownloadStepName].Output.Package.InstallationDirectoryPath”]

28: $databaseStatePath = Join-Path -Path $packagePath -ChildPath “db\state”

If you’re using SQL CI to generate the database NuGet package, then I’m surprised that you’ve found the unpacked database scripts folder at ‘C:\Octopus\Applications\QARLS\sql1_mtx_search\4.47_22’ rather than ‘C:\Octopus\Applications\QARLS\sql1_mtx_search\4.47_22\db\state’. Perhaps you’re generating the NuGet package some other way, say be calling nuget pack directly?

Regardless, I think there are two options to fix this:

  1. Modify your database NuGet package so that the scripts folders are stored in the db\state sub-folder rather than the root of the package. This assumes that you’re not using SQL CI to generate the NuGet package. This is probably the best approach; for future proofing, it’s good practice to avoid storing things directly in the root of a NuGet package.

  2. Modify the PowerShell in the step template. The simplest way to do this would be to replace line 28 with the following:

$databaseStatePath = $packagePath

The down-side to this is that you’ll need to remember to reinstate this tweak if you choose to update to a newer version of the step template sometime in the future.

Let me know if this helps.

Chris

5

Thanks Chris, I realized after I sent my email that I was using Octo.exe to generate my package instead of SCLI. So, I’m making some changes now. Is Oct.exe not recommended any longer?

6

Although at Redgate we’re focusing on users of SQL CI, you’re more than welcome to use Octo.exe. It’s good NuGet practice to avoid polluting the root of a package with content. Instead, there’s a range of conventions for storing different content types in specific sub-folders (e.g. source code, web content, .NET libraries, etc.). We’ve picked db\state for Redgate database script folders. If you stick to that when you create your own NuGet packages, you’re less likely to run into issues such as this.

That said, we do have an item in our backlog to get SQL Release to automatically detect and work around this problem. I’m sorry we didn’t implement it in time for you, but hopefully it will help future users avoid this particular stumbling block. :frowning:

7

That’s all good info, thanks Chris. I’ve gotten the create package step to complete successfully so am going to move forward and believe this ticket can be closed. I appreciate your help.