CI\CD with SQL Server

I did a presentation back in the spring at my user group TALMUG on SQL Server Data Tools. I enjoyed it so much I am doing it at 3 other user groups.

Canadian Technology Triangle .Net User Group  ‎November‎ ‎22‎, ‎2017

North Toronto .net user Group January 10, 2018

Toronto .Net Meetup February 28, 2018

If you want to learn how to work with SSDT and create a CICD pipeline for your SQL Server Database come on out to one of these meetups.

VSTS Compare Excel Spread Sheets

I have many customers who keep spread sheets in version control for various reasons. Why they do it is not the purpose of this post. The point is about using the Compare feature in TFS/VSTS on Excel Spread sheets. I recently discovered an easy way to compare the difference between two spread sheets, and how to hook that up to Visual Studio so I could right click compare right from Team Explorer.

This is for Office 2016. You can do it for older versions however the location of SpreadSheetCompare.exe may be different.

Here is the trick:

Write a batch file that writes the file names of the two files you want to compare to another file. Then call SpreadSheetCompare.exe passing in the file containing the two file names.

dir %1 /B /S > c:\temp\temp.txt 
dir %2 /B /S >> c:\temp\temp.txt 
"C:\Program Files (x86)\Microsoft Office\Root\Office16\DCF\SPREADSHEETCOMPARE.EXE" "c:\temp\temp.txt"

You can store the batch file in version control that way as long as the users get latest on this file they will have it locally. Or just give it to the people who want to do this and they can put it anywhere on their machine.

Hook this up to the Compare menu item in Team Explorer and VS: In Visual Studio open Tools –> Options, navigate to the “Source Control” section specifically “Visual Studio Team Foundation Server”. Open the “Configure User Tools…” dialog.

Add the .xlsx file extension calling your new batch file and passing in the two files to compare (default behaviour).

image

Now when you right click compare an Excel spread sheet in version control you will launch the SpreadSheetCompareTool that comes with office.

The link is to a video showing that. Which I tried to imbed in my blog but there are issues apparently that I don’t have time to research right now.

VSTS/TFS Pipelines

One of my clients just did some stats for their management team.

In 2017 the teams at this client ran 6000+ Builds and 2000+ automated Releases. It really shows just how much TFS has become a part of their Application Lifecycle.

What is more impressive is that these are not just .NET apps. These builds and releases include technologies such as .NET, SQL Server Databases, Neuron (ESB), Dynamics CRM, SSIS, Duck Creek Technologies, GMC, Master Data Services, SharePoint and some I can't recall right now. It's a great story of TFS getting the job done in a not totally Microsoft environment.

So when someone tells you “We don't use VSTS/TFS because we are not a Microsoft shop”. Ask them what that has to do with it and share this story with them.

Two times I #HitRefresh

I’ve been an independent, consultant since 1993. Which means it’s coming up to the 25th anniversary of the first time I #HitRefresh on my career. At that time I was working as an employee with a fairly large data centre in the financial industry, primarily writing teller systems in C for the Credit Unions of Ontario. At this time these systems ran on DOS and OS\2. I #HitRefresh by moving into the consulting world building applications for Windows written in PowerBuilder.

The second time I #HitRefresh was around 2000 when I latched onto the .net wave and learned C# and the .net framework. Along with many other new and exciting tools. Most important to me Team Foundation Server.

This was a big change from the world I had become accustomed. My friend and colleague Barry Gervin and I started speaking at user groups, attending the PDC conferences and going to each others house once a week to teach each other different parts of the .net framework. We eventually started a company that helped out customers adopt this technology. That was the beginning of ObjectSharp Consulting. We offered consulting and training. Some of that training we authored, and some was Microsoft Official Curriculum.

Although there have been many #HitRefresh moments in my career this was the biggest one. I am sure there will be more. Looking back it amazes me how this industry has changed since the mid 80’s when I began writing code. 

This post is part of a series of #HitRefresh moments. Read more at www.HitRefreshbook.com.

Quick and easy custom filtered burn downs

Today I am going to show you how to create quick little burn down charts right off of a work item query.

As you may well know the burn down chart in TFS is based on the remaining work field in the task work item. It queries all the tasks in the corresponding iteration and shows how the remaining work changes each day during the iteration.

This is great if you are a scrum team, it's all you need to know.

I have a lot of customers that use TFS that are not using Scrum or are even that agile. However they love the idea of a burn down report. However they want to see it with different filters applied.

Perhaps by individual or by activity or Area Path. Remember not everyone follows the agile manifesto 100%. Don’t judge. :)

Back to our quick burn downs.

First we need to create a work item query that defines the scope of our burn down. Since it’s a query and not the iteration backlog we can grab any tasks we want we can create a burn down that crosses teams and iterations.

So lets keep it simple for now and create a query that gets all the tasks for the current iteration.

image

Now add the a few columns to the query. We’ll need Remaining Work since that is the key to our Burn Down. Lets also grab Activity, Area Path and Assigned To for our example

image

Now over to the Charts section of the query. (Make sure you have saved the query so you can make a chart.)

Create a new chart and select a Line Trend report.

Under Values change Count to Sum and select Remaining Work as the field to Sum.

Pick your Rolling period. This is how far back you want to start the burn down.

Now select your Group By field. This is where you can define if you want to see the burn down by Activity or Team Member or Area Path.

image

There is no projection beyond today or an ideal trend line, but I am sure you can visualize that.

With charts you can make a bunch of these, each with their own “group by” field to augment the burn down for your teams iteration.  

Writing to the Build Report in TFS 2015

This is a cool trick for writing something directly onto the build report. 

As an example I will write out the heading “ObjectSharp” and under it I’ll put the website. So it will come out looking like this.

image

 

 

 

 

 

 

 

 

 

 

 

The syntax follows this format:

##vso[area.action property1=value;property2=value;...]message

For more examples and syntax look here. For my example I simply added an inline Powershell task. With the following powershell.

$TempFile = [System.IO.Path]::GetTempFileName()
$fullurl = "http://www.ObjectSharp.com" | set-content $Tempfile
Write-Host "##vso[task.addattachment type=Distributedtask.Core.Summary;name=ObjectSharp;]$Tempfile" 

How would I use this. In the past I have used it to write code metric results to the build report. I have also used it to write the URL to a location in Nexus Package Manager where a build artifact was uploaded ready for deployment.

Global Azure BootCamp

GLOBAL AZURE BOOTCAMP

On Saturday, April 22, 2017, communities across the world will be holding Azure Bootcamps. There are already 132 planned!

Here is a list of events happening in Canada: Calgary, Montreal, Ottawa, Quebec, Toronto, Vancouver

Sharing files between Host/VM

Since Windows 8.1 I have had Hyper-V installed on my machine. As an MVP I do a lot of demo’s so this is wonderful. I used to have a spare machine running Windows 2008 just for this.

One thing I have found difficult to do, is share files between my VM and the host machine. All the solutions I was coming across were just not as simple as I would like. Until now. I came across this Blog which outlines 5 ways to achieve this.

Below is, in my opinion the easiest way to share files between a host and VM.

1. Start Hyper-V Manager
2. Right click the Hyper-V host and select "Hyper-V Settings"


image

 

 

 

 

 

 

 

 

3. Ensure the tick box is ticked under Enhanced Session Mode and click OK.

image

 

 

 

 

 

 

4. Right click the virtual machine-> Settings -> Integration Services at bottom left hand side of the menu.
5. Check "Guest Services" and click OK.

image

 

 

 

 

 

 

 

 

 

 

6. Start up the virtual machine, copy files from physical machine and paste in to virtual the machine!

Build and Release Tasks with DropDown Arguments

I have been creating a lot of Build Tasks for the new TFS 2015 Build and Release.

I have discovered a few tricks that were not simple to find. I thought I would Blog about them for my own record and anyone who happens by.

This Blog post makes the assumption that you have already been creating your own build tasks. If not, and you want some background take a look here first.

In this example I created a Release task to perform a DACPAC deployment. The script calls SQLPackage.exe which can do several things. Deploy the changes directly to a Database or generate a Deployment Report, a Drift Report or the SQL that needs to be run to update the target, amongst others.

So I figured I would make my Build Task smart enough to let the end user select which action they would they would like to perform. To do that I need the user to select which action and have that passed into my Powershell script.

The input value that is defined in my task.json file looks like this.

{
   "name": "action",
   "type": "pickList",
   "label": "Action",
   "defaultValue": "Publish",
   "required": true,
   "helpMarkDown": "Select the Action to perform",
   "options": {
     "Publish": "Publish Changes",
     "Script": "Script Changes",
     "DeployReport": "Generate Deployment Report"
   }
 }

The type needs to be “picklist”. I also needed to define options. Each option is comprised of two strings seperated by a semicolon. The string before each semicolon is the value to be returned and the string after the semicolon is the display name. Notice I set one of the values to be the defaultValue.

Once published to TFS the build task inputs will look something like this.

image

 

 

 

 

 

 

 

Now the selected value will be passed into my Script and I can change my call to SQLPackage.exe accordingly.

Making a TFS Build wait

In my career I have created Automated builds for many different technologies. TFS never lets me down. The old XAML build did a good job however the new TFS 2015 Builds are awesome. First of all I love PowerShell, which makes my life much easier. I can just call out to a script sitting in source control or better yet create my own Build Task and upload it to the server.

I recently had to create a build for a development tool that generates documents from various system data. This tool has it’s own editor, version control,  release management everything. It’s kind of a closed eco system.

So how do we get the rest of the team involved in it’s builds and deployments. How do I trigger a build and have this system drop a deployable package for me, that I can deploy to different environments without having to ask the developers? Letting QA be responsible for their own environment.

After talking with an expert in this tool. It turns out, it also has an automation component. The developers can write scripts to perform builds and deployments. However we want this to be part of a larger eco system with our other applications.

It was decided that we would create a file share on the network where I could drop a file to let the other system know I wanted a new build dropped. This way TFS would still generate a build number the testers can deploy to the QA environment and reference from their test plan so we get the full end to end traceability.

So I wrote a script that would drop a file (Build.txt) in a known location on the network. Said products automation tool watches for my file and triggers a script to perform a build when it sees it and then drops that build package back for me to turn into a build artifact. I will put the build number in this file so the other system can use that as a label in it’s own Version Control system.

Then the build waits and watches for a file named BuildComplete.txt to be placed back in that working folder. When my build sees it I know the built package is there and I can create a build artifact. By the way the BuildComplete.txt either contains the word successful or an error log if something went wrong. I will pull that out and toss it up into the build console.

My build has two tasks one to Drop the file that triggers the other system and one to create a build artifact. The second one is already built into TFS “Copy and Publish Build Artifacts”. The first step is one Powershell script. See below with Comments:

The Powershell script takes two arguments The WorkingFolder and a timeout.

image

 

 

 

The comments in line explain how it works.

   1: param()
   2:  
   3: #This is how we do parameters in a custom build task 
   4: Trace-VstsEnteringInvocation $MyInvocation
   5: try {
   6:     Import-VstsLocStrings "$PSScriptRoot\Task.json" 
   7:     [int32]$timeout = Get-VstsInput -Name TimeOut
   8:     [string]$WorkingFolder = Get-VstsInput -Name WorkingFolder 
   9: } 
  10: finally 
  11: { 
  12:      Trace-VstsLeavingInvocation $MyInvocation 
  13: } 
  14:  
  15: #Start the Timer at zero
  16: $timesofar = 0
  17: #This is the file we are looking for to know the package is complete 
  18: $BuildComplete = "$WorkingFolder\BuildComplete.txt"
  19: #Clean the Working Folder
  20: Remove-Item  "$WorkingFolder\*"
  21: #Create file containing Build number to trigger the build
  22: New-Item -path "$WorkingFolder" -name 'Build.txt' -type 'file' -value "$env:BUILD_BUILDNUMBER"
  23:  
  24: Write-Host "Triggered Build"
  25: #While BuildComplete.txt does not exist and timer has not reached the timeout specified
  26: while (!(Test-Path $BuildComplete) -and !($timesofar -eq $timeout)) 
  27: { 
  28:     #Wait for 10 seconds
  29:     Start-Sleep 10 
  30:     #Add 10 seconds to the time So Far
  31:     $timesofar = $timesofar + 10
  32: }
  33:  
  34: #Either the File has been dropped by the other system or the timeout was reached
  35: if (!(Test-Path $BuildComplete)) 
  36: {
  37:     #If the file was not dropped it must be the timeout so fail the build write to the console
  38:     Write-Error "Build Timed out after $timeout seconds."
  39: }
  40: else
  41: {
  42:     #If we got here the file was dropped
  43:     #Get the content of the file
  44:     $BuildResult = Get-Content $BuildComplete -Raw
  45:  
  46:     #Check if it says successful
  47:     if ($BuildResult.StartsWith("successful"))
  48:     {
  49:         #On Success say so
  50:         Write-Host "Successful Build"
  51:         #write the contents out in case the calling system wants to tell us something
  52:         write-Host "$BuildResult"
  53:     }
  54:     else
  55:     {
  56:         #On Error Faile the build and say so 
  57:         Write-Error "Build Error"
  58:         #write the contents out so we can see why it failed
  59:         Write-Error "$BuildResult"
  60:     }
  61: }