User profile for PeterDaniels

Thank you, @Monday. It took me a while to find time to dig into this. I had never looked into the actual files within the nupkg file. Now, thanks to you, I do see the db\output\PackageScript.sql. I also see how it has ALL of the migrations including baseline and will intelligently apply them to whatever DB I set in the sqlcmd var DatabaseName. There are a couple of issues with this file (in its raw form) that I will need to resolve to be able to pass it to the ops DBAs so they can execute in SSMS via SQLCMD mode: 1) I'll need to uncomment the sqlcmd vars section 2) I'll need to manually set the ReleaseVersion and PackageVersion. Not sure why the PackageVersion isn't set by New-DatabaseBuildArtifact cmdlet since we are passing that in. Probably a lil powershell will take care of the above concerns, but it would be nice to have options during build to extract this file with these taken care of. UPDATE: I am also able to get the PackageScript.sq content from the PackageScript property of the BuildArtifact object returned from Invoke-DatabaseBuild. This way I can skip the Export-DatabaseBuild step, and just save this to a file and process it. / comments

Thank you, @Monday. It took me a while to find time to dig into this. I had never looked into the actual files within the nupkg file. Now, thanks to you, I do see the db\output\PackageScript.sql...

0 votes

@Mac_F, thank you the reply. However that does not absans my question. Please see my previous post for clarification. / comments

@Mac_F, thank you the reply. However that does not absans my question. Please see my previous post for clarification.

0 votes

Thank, Mac. I'm using package deployment. I'm using New-DatabaseReleaseArtifact + Export-DatabaseReleaseArtifact. One of the outputs of Export-DatabaseReleaseArtifact is a "TargetedDeploymentScript.sql". This script works for me, but I have to edit it to remove the code that requires a specific target server name. I'm asking if there is a setting in the project file that I can use to have it not include that check so I don't have to edit it each time? Example of the generated code I remove: -- As this script has been generated for a specific server instance/database combination, stop execution if there is a mismatch IF (@@SERVERNAME != 'ATL2100PC0GQ1DZ' OR '$(DatabaseName)' != 'TestSCA_DEV_INT') BEGIN RAISERROR(N'This script should only be executed on the following server/instance: [ATL2100PC0GQ1DZ] (Database: [TestSCA_DEV_INT]). Halting deployment.', 16, 127, N'UNKNOWN') WITH NOWAIT; RETURN; END GOMy PoSh function to remove it: function Remove-LinesFromFileAfterMatch { [cmdletbinding()] param ( [parameter(Mandatory=$true)] [string] $FilePath ,[parameter(Mandatory=$true)] [string] $StringToMatch ,[parameter(Mandatory=$false)] [int] $NumLinesToRemove = 1 ) begin { $TempFile = New-TemporaryFile $LineNum = 0 } process { switch -Wildcard -File $FilePath { $StringToMatch { # We don't want to output this line or the next $NumLinesToRemove $LineNum++ continue } {$LineNum -gt 0 -and $LineNum -le $NumLinesToRemove} { $LineNum++ continue } # Send rest of teh orig file to the new file default {$_ >> $TempFile} } } end { # and now swap the files (tmp -> orig) Copy-Item -Path $TempFile -Destination $FilePath -Force $FilePath } }And using it with the file: $FilePath = "C:\users\peter\tmp\TargetedDeploymentScript.sql" $Args = @{ FilePath = "$FilePath" StringToMatch = "-- As this script has been generated for a specific server instance/database combination, stop execution if there is a mismatch*" NumLinesToRemove = 8 } Remove-LinesFromFileAfterMatch @Args / comments

Thank, Mac. I'm using package deployment. I'm using New-DatabaseReleaseArtifact + Export-DatabaseReleaseArtifact. One of the outputs of Export-DatabaseReleaseArtifact is a "TargetedDeploymentScr...

0 votes

I agree with what @Eddie D said. To add onto that, I would recommend a production db as the "target" when creating the project of you already have a production db. I had some cases where we already had a shared dev db, but not a prod db. For these, I believe the best technique is to not set the "target db" during project creation. Set the development db to your own local, empty db. Then, connect to the shared dev db and import the existing objects as your first migration (rather than baseline). If you do set the shared dev db as the target during project creation, you can run into some deployment challenges down the line related to the "baseline" script if the deployment target is not completely empty (e.g. If you have any users or std objects in your model db). In that case, I have been able to resolve by either removing those objects or by: 1) unchecking "mark first folder as baseline" in the project settings. 2) adding <SkipBaselineCheck>True</SkipBaselineCheck> to the project file xml property group. / comments

I agree with what @Eddie D said. To add onto that, I would recommend a production db as the "target" when creating the project of you already have a production db.I had some cases where we already ...

0 votes

@Monday - I love how your'e thinking here. I was considering adding that functionality to make it more robust, but decided on a quick dev first. / comments

@Monday - I love how your'e thinking here. I was considering adding that functionality to make it more robust, but decided on a quick dev first.

0 votes

I did, @Monday. Here's the function: $VersionInfo = Get-LatestVersionAndMigrationNumber -ProjectFilePath $ProjectFilePath $PackageVersion = $VersionInfo.Version + '-migration' + $VersionInfo.MigrationNumber $ReleaseVersion = $PackageVersion </code># Get latest version and migration from project file path # This is cool. Version like 1.1.0 and Migration number like 5 - to build ReleaseVersion and/or PackageVersion function Get-LatestVersionAndMigrationNumber { [CmdletBinding()] param ( [parameter(Mandatory=$true)] [string] $ProjectFilePath ) process { # First, strip the project file name and find the Migrations folder $ProjectRootFolder = Split-Path -Path $ProjectFilePath $MigrationsFolder = Join-Path -Path $ProjectRootFolder -ChildPath "Migrations" if (-not (Test-Path -Path $MigrationsFolder)) { Write-Error -Message "Invalid path: $MigrationsFolder" exit } # Get the latest migration folder sem ver [string]$Latest = Get-ChildItem -Path $MigrationsFolder -Directory | ForEach-Object {[Version]($_.Name -split "-")[0]} | Sort-Object -Descending | Select -First 1 # Get the base folder back now that I found the latest version: $LatestFolder = Get-ChildItem -Path $MigrationsFolder -Directory | Where-Object {$_.Name -like "$Latest*"} # Now get the migrations in that folder $LatestMigrationsFolder = Join-Path -Path $MigrationsFolder -ChildPath $LatestFolder $LatestMigration = (Get-ChildItem -Path $LatestMigrationsFolder -File).Name | Sort-Object -Descending | select -First 1 [int]$LatestMigrationSequenceNumber = ($LatestMigration -split "_")[0] # Return a custom object with the latest sem version and migration sequence num #$Latest + "-migration" + $LatestMigrationSequenceNumber $obj = New-Object -TypeName PSCustomObject -Property @{Version = $Latest;MigrationNumber = $LatestMigrationSequenceNumber} $obj } } </pre><div>I then use it:<br><pre class="CodeBlock"><code>To create a version that looks like (for example) 1.1.0-migration14 / comments

I did, @Monday. Here's the function:$VersionInfo = Get-LatestVersionAndMigrationNumber -ProjectFilePath $ProjectFilePath $PackageVersion = $VersionInfo.Version + '-migration' + $VersionInfo.Migrati...

0 votes

Bummer. I'd like to create coherent concept of "version" from SCA to Bamboo to Octo as well as in the __MigrationLog. Frankly, I want it to integrate with DLM Dashboard, too. But...I digress. I think I'll have to add a custom build step to recurse through the migrations folder to look for the latest sem ver folder name.... / comments

Bummer. I'd like to create coherent concept of "version" from SCA to Bamboo to Octo as well as in the __MigrationLog. Frankly, I want it to integrate with DLM Dashboard, too. But...I digress.I t...

0 votes

Thank you, @Mike U! I think this falls under the umbrella of needing to update the docs. I'd be willing to help you and the team with this process. Cheers. -Peter / comments

Thank you, @Mike U! I think this falls under the umbrella of needing to update the docs. I'd be willing to help you and the team with this process. Cheers.-Peter

0 votes

Thanks, team. Monday, I saw that part of the docs and assumed it meant that ReleaseVersion would be automatically populated, but that was not happening. I got it working by manually creating a variable in Octopus - ReleaseVersion, and setting it to #{Octopus.Release.Number} Now, I'm seeing it populated in the dbo.__MigrationLog table (e.g. "1.1.0-build22") Which came from Bamboo via the Package Version: 1.1.0-build${bamboo.BuildNumber}. Making some progress... Now, I just want to find the right way to populate the "1.1.0" in Bamboo from the SCA project...probably another post, tho... / comments

Thanks, team. Monday, I saw that part of the docs and assumed it meant that ReleaseVersion would be automatically populated, but that was not happening. I got it working by manually creating a v...

0 votes

Thank you, Mike. So, I need to create an Octopus variable called ReleaseVersion and set it? Also wondering how I get the version info from the existing variables in Octopus in order to set ReleaseVersion? Edit: I created an Octoput variable, ReleaseVersion and set its value to: #{Ocopus.Release.Id} This just set it to Release.26, even though the name of the release was 1.1.0-build22...looking for another var... / comments

Thank you, Mike. So, I need to create an Octopus variable called ReleaseVersion and set it? Also wondering how I get the version info from the existing variables in Octopus in order to set Releas...

0 votes

How can we help you today?

PeterDaniels

Comments