search

microsoft / azure-pipelines-tasks

Tasks for Azure Pipelines
https://aka.ms/tfbuild
MIT License
3.49k stars 2.61k forks source link

SqlDacpacDeploymentOnMachineGroup task fails to detect SQLPackage.exe in VS2017 (15.0) #6107

Closed iknyc closed 6 years ago

iknyc commented 6 years ago

Troubleshooting

The Release task SqlDacpacDeplopymentOnMachineGroup fails to find sqlpackage.exe on the machine, despite the fact that it is installed via VS2017.

Agent = Private (DeploymentGroup) [version: vsts-agent-win-x64-2.126.00]:

CPU Status OK OS Name Microsoft Windows Server 2016 Standard OS Service Pack 0.0 OS System Drive C: OS Version 10.0.14393 OS Windows dir C:\Windows

Issue Description

The SqlPacakgeOnTargetMachines.ps1 script downloaded as part of the task seems to look in the registry for a string value associated with "InstallDir" in the version root for Visual Studio (10.0, 12.0, 14.0, 15.0). After investigating additional machines, "InstallDir" is only present up through version 14.0. Since VS2017 is version 15.0, InstallDir is never located, and the task fails.

Error logs

The following is the output running the powershell code manually via VS Code on the target machine in the machine group. The code is run from the agent _tasks directory with increased verbosity:

$VerbosePreference="Continue"
PS (E:\SqlDacpacDeploymentOnMachineGroup)> Get-SqlPackageOnTargetMachine

VERBOSE: Sql Versions installed on machine [REMOVED]as read from registry: 110 VERBOSE: Dac Framework (installed with SQL) not found on machine [REMOVED] VERBOSE: Dac Framework (installed with DAC Framework) not found on machine [REMOVED] VERBOSE: Visual Studio versions found on machine [REMOVED] as read from registry: 15.0 12.0 10.0 8.0 VERBOSE: Dac Framework (installed with Visual Studio) not found on machine [REMOVED]

Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine [REMOVED] At line:81 char:9 throw "Unable to find the location of Dac Framework (SqlPack ...


    CategoryInfo          : OperationStopped: (Unable to find ...achine SRPSTS52:String) [], RuntimeException
   FullyQualifiedErrorId : Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine [REMOVED]

A successful execution would reveal the install location of visual studio per line 323 of SqlPacakgeOnTargetMachines.ps1:

Write-Verbose "Visual Studio install location: $vsInstallDir"

Yielding an output of VERBOSE: Visual Studio install location: [Location Here]

In order to get the script to work correctly, I've temporarily created the "InstallDir" string in the registry:

$vsRegKey = "HKLM:", "SOFTWARE", "Wow6432Node", "Microsoft", "VisualStudio" -join [System.IO.Path]::DirectorySeparatorChar
$vsRegKey64 = "HKLM:", "SOFTWARE", "Microsoft", "VisualStudio" -join [System.IO.Path]::DirectorySeparatorChar

and I set the value to be E:\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\ (The location I have VS2017 installed). After doing so, this is the output of the script:

$VerbosePreference="Continue"
PS (E:\SqlDacpacDeploymentOnMachineGroup)> Get-SqlPackageOnTargetMachine

VERBOSE: Sql Versions installed on machine [REMOVED] as read from registry: 110 VERBOSE: Dac Framework (installed with SQL) not found on machine [REMOVED] VERBOSE: Dac Framework (installed with DAC Framework) not found on machine [REMOVED] VERBOSE: Visual Studio versions found on machine [REMOVED] as read from registry: 15.0 12.0 10.0 8.0 VERBOSE: Visual Studio install location: E:\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\ VERBOSE: Dac Framework installed with Visual Studio found at E:\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe on machine [REMOVED]

... and the task works.

The agent has SqlPackage registered as a capability:

Capability Name Capability Value
PROXY http://########:80
PSModulePath %ProgramFiles%\WindowsPowerShell\Modules;C:\Windows\system32\WindowsPowerShell\v1.0\Modules;C:\Program Files\WindowsPowerShell\Modules\;C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ResourceManager\AzureResourceManager\;C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\;C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\Storage\
PUBLIC C:\Users\Public
SqlPackage E:\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe
SystemDrive C:
SystemRoot C:\Windows

Please modify the task to search for SqlPackage using a different method.

arjgupta commented 6 years ago

@iknyc , I have filed a bug for this. Right now, for unblocking yourself, you can download the data tier application framework (DacFx) from here

arjgupta commented 6 years ago

@iknyc, were you unblocked after installing DacFx?

asranja commented 6 years ago

@iknyc are you still facing the issue ?

chshrikh commented 6 years ago

closing this, please feel free to get back

iknyc commented 6 years ago

@asranja @chshrikh apologies for the excessively late response. Should I be testing / validating a fix you merged into master?

If you haven’t deployed a fix, then the answer is no, but with a caveat. I’ve bypassed the flaw in the code logic by artificially creating the registry key the script was looking for. If I delete this registry key, and attempt to reproduce the issue without any remediation from your team, I will indeed be having the same issue.

Please advise! Thank you.