function Get-SqlPackagePath {
    Find path to specific version of SqlPackage.exe
    Finds the path to specific version of SqlPackage.exe
    Checks the following locations:
        ${env:ProgramFiles}\Microsoft SQL Server\*\Tools\Binn
        ${env:ProgramFiles(x86)}\Microsoft SQL Server\*\Tools\Binn
        ${env:ProgramFiles(x86)}\Microsoft SQL Server Management Studio *\Common7\IDE
    The environment variable $env:CustomSqlPackageInstallLocation allows you to specify your own custom install directory.
    For information on SqlPackage.exe see
    .PARAMETER Version
    Defines the specific version of SqlPackage.exe to which you wish to obtain the path.
    Valid values for -Version are: ('15', '14', '13', '12', '11') which translate as follows:
    * 15: SQL Server 2019
    * 14: SQL Server 2017
    * 13: SQL Server 2016
    * 12: SQL Server 2014
    * 11: SQL Server 2012
    If you are unsure which version(s) of SqlPackage.exe you have installed, use the function **Find-SqlPackageLocations** to obtain a full list.
    The full path to the specific version of SqlPackage.exe you requested
    Get-SqlPackagePath -Version 13
    Returns the path to the SQL Server 2016 version of SqlPackage.exe (if present on the machine).
    Get-SqlPackagePath -Version latest
    Return the full path to a latest version of SqlPackage.exe
    Written by (c) Dr. John Tunnicliffe, 2019-2021
    This PowerShell script is released under the MIT license

        [Parameter(Mandatory = $true)]
        [ValidateSet('150', '140', '130', '120', '110', '15', '14', '13', '12', '11')]

    [string] $ExeName = "SqlPackage.exe";
    [string] $SqlPackageExePath = $null;
    $Version = $Version.Substring(0,2);
    # always return x64 version if present
    [System.IO.FileSystemInfo[]]$SqlPackageExes = Get-Childitem -Path "${env:ProgramFiles}\Microsoft SQL Server\*\Tools\Binn" -Recurse -Include $ExeName -ErrorAction SilentlyContinue;

    $SqlPackageExes += Get-Childitem -Path "${env:ProgramFiles(x86)}\Microsoft SQL Server\*\Tools\Binn" -Recurse -Include $ExeName -ErrorAction SilentlyContinue;

    $VsPaths = Resolve-Path "${env:ProgramFiles(x86)}\Microsoft Visual Studio\*\*\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\";
    foreach ($VsPath in $VsPaths) {
        $SqlPackageExes += Get-Childitem -Path $VsPath -Recurse -Include $ExeName -ErrorAction SilentlyContinue;
    # Custom install location defined by Environment variable CustomSqlPackageInstallLocation
    $CustomInstallLocation = [Environment]::GetEnvironmentVariable('CustomSqlPackageInstallLocation');
    if ("$CustomInstallLocation" -ne "") {
        if (Test-Path $CustomInstallLocation) {
            $SqlPackageExes += Get-Childitem -Path "$CustomInstallLocation\" -Recurse -Include $ExeName -ErrorAction SilentlyContinue;
        } else {
            throw "Invalid custom environment variable path: CustomSqlPackageInstallLocation";

    foreach ($SqlPackageExe in $SqlPackageExes) {
        $ExePath = $SqlPackageExe.FullName;
        [string] $ProductVersion = $SqlPackageExe.VersionInfo.ProductVersion.Substring(0,2);      
        if ($ProductVersion -eq $Version) {
            $SqlPackageExePath = $ExePath;
            Write-Verbose "$ExeName version $Version found here: $SqlPackageExePath";       
    return $SqlPackageExePath;