PowerShell Snippet #4



Below is a function that will search in  a string and replace a character in a variable. Simple enough. It generally saves time in calling this from a helper file in source control for tidying up variables that are passed in to a Powershell function that aren’t quite formatted as required. A good example of this is upstream build variables with spaces in TeamCity.

Function FormatReplaceVariable

Write-Host "Replacing characters $find with $substitute in $param" -Verbose
$param = $param -replace "$find", "$substitute"
Write-Host "Now parameter looks like $param" -Verbose
return $param

$bob = FormatReplaceVariable -param "as'lfjksaj'JASD;oasdjf'asd aasd asd; ghas'fkasdf;aksd" -find ";" -substitute "!!!!"
write-host $bob

PowerShell Snippet #3


Today’s snippet is a two-for-one type deal: connecting to SQL Server and running a query to get datetime. Really, it’s just useful for checking connectivity issues.

$dataSource = “[serverInstance]”

$database = “[database]”
$connectionString = “Server=$dataSource;Integrated Security=True;Database=$database;”
write-host $connectionString -Verbose

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString


$query = “SELECT GETDATE() AS TimeOfQuery;”

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”

Alternative to use InvokeSqlCmd:

Import-Module sqlps
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "[serverInstance]; Trusted_Connection=True; Initial Catalog=[database]" 

SSAS Activity Monitor Available

So it’s been a while since I’ve worked on any SSAS, but today I am happy to announce that there is an up-to-date version of the SSAS Activity Monitor. The solution has had couple of changes to it.

  • The Analysis Services dll has been updated to SQL Server 2016. So this versions will work with 2012, 2014 and 2016 instances of SSAS.
  • The .Net target has been altered from 4.0 to 4.5.1.
  • There is an exe that is outputted as part of the build.
  • A redundant project has been removed.
  • The code has been moved to github.

If you have been through the commits you’ll see that Marco Russo has been contributing most of this. To be honest, since I have not worked with SSAS directly in about 2 years now, I’ve not been terribly interested in developing any features to this project. So the fact tha someone in the BI Cmmunity has actively sought out to help out in maintaining this project is a big help, and so anyone else who wants to contribute I encourage you to get in touch with Marco or myself  to make this project even better.


Deploy Cubes Using Powershell: The Script


Recently someone got in touch with me about one of my earliest posts. I realised I had made a mistake on that post and had updated. But I also realised that the script itself is actually part of a PowerShell module. So wanting to make things as easy as possible, and being the helpful guy that I am, I have altered that example into a script which I am posting here.

It’s worth stressing that the errors that output to the console are fairly generic, and that there is a log file that will contain all the information if there are any errors.

Continue reading “Deploy Cubes Using Powershell: The Script”

Free Microsoft eBooks

Yep, no sooner had Microsoft decided to lay-off their editorial staff do they decide to giveaway for free a considerable amount of eBook content. Obviously there is probably not a link between the two events, as clearly Microsoft have been giving away free eBooks for years, but it would be remiss for me to ignore Microsoft’s decision whilst distributing free content. The first link is a very good read and I encourage you to peruse.

Free UK Visual Studio/ALM Events in 2016

Throughout the second half of this year, Microsoft are hosting three free events on the popular topics of Application Lifestyle Management & DevOps with Visual Studio. These are full day events, and yes to answer the most important question snacks and lunch is free!

It’s not entirely clear if the event is going to include Visual Studio Team Services, however there is the promise of showing off new features. So presumably another update for Visual Studio 2015 is coming soon, or new features are going to be added to VSTS. But the main gist of the day is the end-to-end application lifecycle with Microsoft tooling, taking an application from requirements, development to automated release and testing

Full info on these days are here.

Using TeamCity To Raise Octopus Defects Updated


I recently updated the script that uses both the TeamCity and Octopus API’s to raise defects if a TeamCity build has failed (the reasoning here is tat a deployment has passed on Octopus, but subsequent automated testing has failed.)

This script uses functions and is a little neater than the previous version. You will have to create a snapshot dependency between the build that this step runs in and the build that deploys/runs the tests. You will also need to add parameters to the TeamCity build that runs this script to populate the parameters.

Essentially the “UpdateDefects” function is called, and if that finds that the build has failed then it will run “RaiseDefect”.

That’s all for today, have a good one. Happy scripting!

[parameter(Mandatory=$true)] [string]$buildType,
[parameter(Mandatory=$true)] [string]$dependentBuildNumber,
[parameter(Mandatory=$true)] [string]$epicFail,
[parameter(Mandatory=$true)] [string]$root,
[parameter(Mandatory=$true)] [string]$user,
[parameter(Mandatory=$true)] [string]$pass,
[parameter(Mandatory=$true)] [string]$api,
[parameter(Mandatory=$true)] [string]$OctopusApiKey,
[parameter(Mandatory=$true)] [string]$ProjectName,
[parameter(Mandatory=$true)] [string]$ReleaseVersion

Function UpdateDefect
$build = "$buildType #$dependentBuildNumber"
$secpasswd = ConvertTo-SecureString $pass -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($user, $secpasswd)
$result = Invoke-WebRequest $root -Credential $credential -UseBasicParsing
[xml]$xml = $result.Content
[string]$status= $xml.builds.build | select -first 1 | Format-Table -AutoSize -Property status -HideTableHeaders | Out-String
$status = $status.Trim()
if ($status -eq $epicFail)
write-host "Build $build Failed. Raising a defect."
elseif ($status -ne $epicFail) {write-host "Build $build has not failed. Not raising a defect."}
Function RaiseDefect
[System.Uri]$defectMessage = "This is a defect raised by failing TeamCity build $build. Please refer to the logs of the build for the reason of the failure."
$header = @{ "X-Octopus-ApiKey" = $OctopusAPIKey } #header for web requests
$defectDescription = @{ description = $defectMessage } | ConvertTo-Json #description of defect is required when posting new defect

#retrieve project id; required for getting release
$projectUri = "$api/api/projects/$ProjectName"
$Project = Invoke-WebRequest -Uri $projectUri -Headers $header -UseBasicParsing | ConvertFrom-Json
$ProjectId = $Project.Id

$relUri = "$api/api/projects/$ProjectId/releases/$ReleaseVersion"
$releaseDetails = Invoke-WebRequest -Uri $relUri -Headers $header -UseBasicParsing | ConvertFrom-Json

$reportDefect = $releaseDetails.Links.ReportDefect #url to report defects
$defectApiUrl = $releaseDetails.Links.Defects #url to get info on defects
$resolveDefect = $releaseDetails.Links.ResolveDefect #url to resolve defects

$getDefectUrl = "$api$defectApiUrl" #make full defect url
$reportDefectUrl = "$api$reportDefect" #make full report url
$resolveDefectUrl = "$api$resolveDefect" #make full resolve url

#check if unresolved defect exists. If it does, then write-error and exit script
$StatusesofDefects= Invoke-WebRequest -Uri $getDefectUrl -Method Get -Headers $Header -UseBasicParsing | ConvertFrom-Json
foreach ($DefectStatus in $StatusesofDefects.Items.Status)
if ($DefectStatus -eq 'Unresolved')
Write-Error "An unresolved Octopus defect already exists. Resolve previous Octopus defects before attempting to raise another defect."
#we'll only get here if there are no unresolved defects
write-host "raising defect for project" $ProjectName "for release version" $ReleaseVersion
Invoke-WebRequest -Uri $reportDefectUrl -Method Post -Headers $Header -Body $defectDescription -UseBasicParsing | ConvertFrom-Json
#commented out resolve defect. We don't need it to run, but it's here for future reference.
#Invoke-WebRequest -Uri $resolveDefectUrl -Method Post -Headers $header | ConvertFrom-Json


Into The [Void]

Recently I’ve been wondering how I can suppress the output in my PowerShell scripts when loading assemblies into them. I used to find them useful; but now I find them annoying and they are no substitute for error handling ( I used to find them handy as a way of telling me that the script had got this far in the script).

There is more than one way to suppress output to the console, but for assembly loading, I prefer to use [void] because it looks neater than the alternatives:


What is happening here is that [void] is casting the output into null so it does not appear on the console. Remove the [void] and you will see the output.


I mentioned that I prefer the neatness of this to the alternatives, so what are the alternatives? This post on StackOverflow not only talks about the alternatives, but also performance tests the alternatives against one another. It makes for interesting reading!

SQL Relay 2016

SQL Relay returns again at October this year. If you’re unfamiliar with SQL Relay, it is a series of 1 day events that are run throughout the UK. Each event is a single track conference, with between 50 and 200 attendees and 6-8 hour long talks by SQL Server professionals, MVPs, authors, technical experts etc.

Thanks to the sponsors, this event is free to attend. Shockingly, there is no London event, and being the London-centric chap that I am, I probably won’t be attending any of the days this year. This will be the first time in 3 years I won’t be going.

There are speaking opportunities and applications are open until the middle of August. If you’re interested in speaking you can find out details about it here.