@mentions in VSTS/TFS

I find a lot of people don’t know about @mentions. @mentions allows you to bring someone into the conversation around a work item.  All you have to do is type an at symbol “@” into the discussion field.

Once you start typing the user search will appear similar to when you start typing a user name into the assigned To field.


Once you find the person that you want to bring into the conversation you just mention them in the Discussion field.


The person you mentioned will receive an email letting them know they were mentioned.


You can read all about it here.

VSTS Sync Migration Tools

If you need to bulk edit and migrate data between Team Projects on both Microsoft Team Foundation Server (TFS) and Visual Studio Team Services (VSTS), try VSTS Sync Migration Tools. It's not the most user friendly tool to use, but it's VERY powerful and flexible.

What can you do with this tool?

  • Assist in Bulk Editing of Work Items
  • Migrate Work Items & Test Management from one Team Project to another
  • Merge Team Projects
  • Migrate Work Items & Test Management from one account or collection to another
  • Assist in changing process templates


You can install this tool, by simply running Chocolatey command as such: choco install vsts-sync-migrator or download the latest release from GitHub and unzip. You can also obviously contribute to the tool: https://github.com/nkdAgility/vsts-sync-migration

A few tips on using the tool:

  • You need to add the account that you use to Project Collection Services Accounts group using tfssecurity command
  • You also need to add ReflectWorkItemID field to your source and destination. This field is technically not required, but since it is used to make sure that work items are not migrated more than once when you re-run the tool more than once, I find it very important to add. Add this field to the destination if you're syncing work items one way, and add this field to both source and destination if you're syncing bi-directionally. More information about server configuration for the tool, see http://vsts-bulk-editor.readthedocs.io/en/latest/server-configuration/
  • This tool is based on processors where you can load a specific processor to do something you need:
    • Use WorkItemMigrationContext processor to migrate the tip revisions of the work items
    • Use WorkItemRevisionReplayMigrationContext processor to migrate the work items with history
    • Use WorkItemUpdate processor to bulk edit the work items
    • Use AttachementExportMigrationContext processor to export all work items attachments to the migration machine. This is used in partnership with the AttachmentImportMigrationContext
    • Use AttachementImportMigrationContext processor to import all work items attachments from the migration machine. This is used in partnership with the AttachementExportMigrationContext
    • Use LinkMigrationContext processor to migrate all the work item links, both between work items and external links.
    • Use WorkItemQueryMigrationContext processor to migrate all shared work item queries

This tool has a bunch of other processors dealing with test objects, Git links, teams, etc. as well as various field mapping options as I said this tool is very powerful. We'll cover those in one of the future posts.




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).


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.

witadmin and Visual Studio 2017

Ordinarily, witadmin tool is stored under %programfiles(x86)%\Microsoft Visual Studio XX.0\Common7\IDE path as per https://docs.microsoft.com/en-us/vsts/work/customize/reference/witadmin/witadmin-customize-and-manage-objects-for-tracking-work, but in case of the Visual Studio 2017 it's stored under %programfiles(x86)%\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer. Notice it's now stored in the same folder where all Visual Studio extensions are stored. This makes sense considering that Process Editor is now a Visual Studio extension instead of a separate install/download. It's a small, but important change. Just thought I'd share. Thanks for reading.

Another task in VSTS/TFS

As I help people create awesome build and release pipelines, I have found myself quite often needing to download something from somewhere to do something awesome. So, I have decided to write a build task to make it easier. Along came the Download File task: https://marketplace.visualstudio.com/items?itemName=automagically.DownloadFile. This task allows you to download a file from anonymous sources or authenticated sources like Artifactory, or Nexus, or whatever. It's simple and yet pretty good task.

Later, I have realized that quite often when I download the file as a part of my build/release, I need to extract that file. So, I have released another task called Download and Extract File: https://marketplace.visualstudio.com/items?itemName=automagically.DownloadExtractFile. Yes, I know it's not very creative name, but it's very descriptive. This task allows you to download a file from anonymous or authenticated sources and then extract it to the specified folder. This is a better task, I think.

Thanks for using my tasks. And, again thanks for reading.

Azure AD sync errors for administrative user accounts

This is a follow up blog post to the "Insufficient access rights to perform the operation error in Azure AD Connect" blog post I did a little while back. The original blog post covered Azure AD sync errors. For the most part, anyways, because we kept seeing sync errors for specific users. After doing a bit of digging, we have learned that the users that kept getting sync errors belong to Active Directory Administrators or Domain Admins groups. This lead our troubleshooting journey to learning about Active Directory Protected Groups.

As it turns out, if a user is a member of Active Directory Administrators or Domain Admins groups, then Active Directory will overwrite any ACL changes that you make with predefined ACL template on a regular basis. So, if we make ACL changes like grant additional permissions on those accounts or enable permissions inheritance on those accounts in order to allow Azure AD Connector to update source anchor (ms-ds-consistencyGuid) attribute, this change will be overwritten by Active Directory, which brings us back to square one. To bypass this issue, you can do the following:

  • Change ACL template in Active Directory to include the changes you need like Replicate Directory Changes and Replicate Directory Changes All permissions for Azure AD Connector account and write permissions for source anchor (ms-ds-consistencyGuid) attribute. This would work, but in my opinion, it's a bit too drastic
  • Or, remove the users from Active Directory Administrators or Domain Admins groups, if you can.
  • Or, you can make the permissions changes on those accounts and immediately force Azure AD Connect sync using the following PowerShell command: Start-ADSyncSyncCycle -PolicyType Initial. Azure AD Connect should have enough time to write to source anchor attribute and complete the sync without errors. After the initial sync is complete, AD can reset ACL back on those account all it wants since we only need to write to source anchor attribute once.

I prefer the last option, since it's simple and it works. More information about AD protected groups can be found at https://technet.microsoft.com/en-us/library/2009.09.sdadminholder.aspx

Thanks for reading.

Insufficient access rights to perform the operation error in Azure AD Connect

If you're getting Insufficient access rights to perform the operation in your Azure AD Connect synchronization logs, do the following:

  • Make sure you have the latest version of Azure AD Connect installed: https://www.microsoft.com/en-us/download/details.aspx?id=47594
  • If you're syncing passwords, make sure that your sync service account has Replicate Directory Changes and Replicate Directory Changes All permissions in your on premises Active Directory
  • Make sure that your sync service account has write permissions on your sourceAnchor attribute (which is most likely set to ms-ds-consistencyGuid). You can do that either using the user interface, or PowerShell, which is easier:

    $accountName = "DOMAINNAME\USERNAME" #[this is the account that will be used by Azure AD Connect Sync to manage objects in the directory.


    $cmd = "dsacls '$ForestDN' /I:S /G '`"$accountName`":WP;ms-ds-consistencyGuid;user'"

    Invoke-Expression $cmd

  • Make sure that inheritance is turned on for the AD objects that get errors in the synchronization logs. To do that
    • Open Active directory Users and Computers
    • Enable the Advanced features in the View settings and,
    • Open up the user object that can't sync.
    • Go to the security tab and then into advanced
    • Check to make sure the box is checked to inherit permissions. But before you do that make sure that the enabling inheritance will not bring down some permissions that you do not want to be there

That's all. Thanks for reading.

VSTS/TFS task to execute SQL scripts

I have published VSTS/TFS build task that allows you to execute SQL script as a part of your build or release pipelines. What makes this task different from similar tasks in VSTS marketplace is that you can execute SQL script in the number of ways:

  • Execute SQL script from the agent using integrated Windows authentication
  • Execute SQL script from the agent using SQL authentication
  • Execute SQL script from the remote server using Windows authentication, which is handy when you have to do cross domain deployments and/or when you do not have SQL client tools installed on the agent
  • Execute SQL script from the remote server using SQL authentication, which is handy when you do not have SQL client tools installed on the agent

The tool is free and it works. You can download it from VSTS marketplace.

Please let me know if you have any comments or find any bugs. Thanks for reading.