SqlAzureDacpacDeploymentV1 doesn't check for VS2022 installation when searching for sqlpackage location using vswhere

microsoft / azure-pipelines-tasks

Tasks for Azure Pipelines

https://aka.ms/tfbuild

MIT License

3.47k stars 2.6k forks source link

SqlAzureDacpacDeploymentV1 doesn't check for VS2022 installation when searching for sqlpackage location using vswhere #15998

Open PaulVrugt opened 2 years ago

PaulVrugt commented 2 years ago

Required Information

Entering this information will route you directly to the right team and expedite traction.

Question, Bug, or Feature?
Type: Bug

Enter Task Name: SqlAzureDacpacDeploymentV1

Environment

N/A

Agent - Private agent (but N/A)

Issue Description

SqlAzureDacpacDeployment@1 doesn't query for visual studio 2022 installation using vswhere. This was fixed before for Visual Studio 2019 in https://github.com/microsoft/azure-pipelines-tasks/pull/10456 (see https://github.com/microsoft/azure-pipelines-tasks/issues/10438)

Task logs

When only visual studio 2022 is installed, running the SqlAzureDacpacDeployment@1 task results in an error:

[error]Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine xx Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

Error logs

[error]Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine xx Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

github-actions[bot] commented 2 years ago

This issue is stale because it has been open for 180 days with no activity. Remove the stale label or comment on the issue otherwise this will be closed in 5 days

PaulVrugt commented 2 years ago

This is only stale because nobody is reacting to this bug

stevenosan commented 2 years ago

This error started happening on our builds when this task was updated from 1.202.0 to 1.209.0

I am assuming we were not seeing this prior to the release of 1.209.0 since we had both Visual Studio 2019 and Visual Studio 2022 installed.

As a stopgap solution, we created a 150 directory in the Visual Studio 2022 installation to mimic the directory layout of Visual Studio 2019.

We believe the problem lies in the if statement at line 321 where it is probably looking for those 150, 140, 130, directories that do not exist in the Visual Studio 2022 installation directory.

msjf commented 1 year ago

The ExecuteSql task : • Only considers the most recent version of visual studio (see https://github.com/microsoft/azure-pipelines-tasks/blob/5b17d46ad266e2ee73b3700cb1d5101787dd588d/Tasks/SqlAzureDacpacDeploymentV1/FindSqlPackagePath.ps1#L444, latest param of vswhere) • assumes that there are versioned directories of the dacpac binaries (see https://github.com/microsoft/azure-pipelines-tasks/blob/5b17d46ad266e2ee73b3700cb1d5101787dd588d/Tasks/SqlAzureDacpacDeploymentV1/FindSqlPackagePath.ps1#L361)

It explains the break as

In 2022 installation, there is no directory with version
There is no fallback to the 2019 install directory, which previously worked

I think there are two solutions that would work and would both be suitable for a robust operation

allow for searching sqlpackage.exe at the root of the install directory (no versionned dir as suggested above)
scan all installed versions of VS in case the latest one may have been installed without dacpac for some reasons.

github-actions[bot] commented 1 year ago

This issue is stale because it has been open for 180 days with no activity. Remove the stale label or comment on the issue otherwise this will be closed in 5 days

PaulVrugt commented 1 year ago

Still stale because nobody is fixing this

AartBluestoke commented 1 year ago

a variant of this problem - i have visual studio installed, which has an old sqlpackage version. if you install the official sqlpackage via dotnet tool install --global Microsoft.SqlPackage --version 162.0.52 it doesn't get found, even though there is now a C:\Program Files\Microsoft SQL Server\160\Shared folder.

bjorkholm-g commented 8 months ago

Any solution to this issue?

AartBluestoke commented 8 months ago

Its been a few months - i think installing sqlPackage with the MSI installer from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download rather than 'dotnet tool' works (a bunch of different things was tried ...) - also keep in mind that libraries are specific to versions of sqlPackage - extension dll's are not binary compatible between the MSI version (donet framework based) and the other (standard/core based)

bjorkholm-g commented 8 months ago

Thank you for the hint. I have not found any solution yet, but I actually think there might be an issue with the Virtual Machine disc image I'm using. I'm trying to run this with a Microsoft Visual Studio 2022 image for an Azure Virtual Machine Scale Set Agent, but there I get the above error. However, I do not face any errors with a windows 2022 Azure DevOps Agent.

vanmash commented 6 months ago

Any progress?

bjorkholm-g commented 6 months ago

As stated previously; the workaround here is to update the sqlpackage via the MSI. In YAML this is done correctly via:

        # Upgrade SqlPackage.exe
        - task: PowerShell@2
          displayName: 'Upgrade SqlPackage.exe'
          inputs:
            targetType: 'inline'
            script: |
              # Install msi package of DacFx framework. The msi contains the SqlPackage.exe.
              # Download, from Microsoft, and run the DacFramework.msi installer for Windows.
              wget -O DacFramework.msi "https://aka.ms/dacfx-msi"
              msiexec.exe /i "DacFramework.msi" /qn

              ....and then run the deployment via a task using SqlAzureDacpacDeployment@1