Import-D365Bacpac cannot find SqlPackage

[colind-work]

I tried to import a backup of a cloud-hosted D365FO environment into the dev environment VM. See Powershell commands and output below.

PS C:\Users\localadmin> Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\users\localadmin\downloads\UATbackup.bacpac" -NewDatabaseName "ImportedDatabase" -ShowOriginalProgress [04:22:26][Test-PathExists] The C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe path wasn't found. Please ensure the path exists and you have enough permission to access the path. WARNING: [04:22:26][Test-PathExists] Stopping because of missing paths. [04:22:36][Set-SqlBacpacValues] Something went wrong while working against the database. | Exception calling "Open" with "0" argument(s): "Cannot open database "ImportedDatabase" reque sted by the login. The login failed. Login failed for user 'DEVMACHINE\localadmin'." WARNING: [04:22:36][Set-SqlBacpacValues] Stopping because of errors. | Something went wrong while working against the database.

PS C:\Users\localadmin> sqlpackage /version 162.1.172.1

PS C:\Users\localadmin>

The problem is that the script is looking for SqlPackage at C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe, although on the VM it is installed at C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe. I also have it installed in the system PATH as you can see in the ` sqlpackage /version' command above.

[Splaxi]

We have a command, that downloads the "current" stable SqlPackage.exe and places it on the c:\temp\d365fo.tool\SqlPackage

Invoke-D365InstallSqlPackage

https://github.com/d365collaborative/d365fo.tools/blob/master/docs/Invoke-D365InstallSqlPackage.md

[colind-work]

That seems to have worked. I installed it, updated my PATH to include c:\temp\d365fo.tool\SqlPackage and now Import-D365Bacpac has been running for several minutes.

[colind-work]

You can close the ticket.

[Splaxi]

Updating the path isn't needed for the d365fo.tools - that is only for the sake of cmd/powershell - when you want to type sqlpackage /version and have it show the version.

The powershell module has internal variables and references, and these are updated, when you run the Invoke-D365InstallSqlPackage

[FH-Inway]

@Splaxi I think we should review the location that d365fo.tools uses as default location for SqlPackage.exe.

https://github.com/d365collaborative/d365fo.tools/blob/13cf34d8cc8640fd5586a53aa2efeb55614540b2/d365fo.tools/internal/configurations/configuration.ps1#L32

This location does not seem to be valid anymore. We might be able to use e.g. C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe. But I think a better way would be to extend the check whether SqlPackage.exe is available in $Script:SqlPackagePath whenever Invoke-SqlPackage is called. If it isn't available, it should show a helpful warning/error message to the user that suggest calling Invoke-D365InstallSqlPackage. Or it should just automatically call it.

[colind-work]

I also think that you should find SqlPackage.exe wherever it is installed, if it is in your Windows PATH. In the article Download and install SqlPackage, Microsoft says (my emphasis):

Installing SqlPackage as a dotnet tool requires the .NET SDK to be installed on your machine. Installing SqlPackage as a global tool makes it available on your path as sqlpackage and is the recommended method to install SqlPackage for Windows, macOS, and Linux. SqlPackage is available as a dotnet tool for .NET 6 and .NET 8.

[FH-Inway]

@colind-work Thanks for the suggestion. I agree that when SqlPackage.exe is available in the Windows PATH environment variable, it should be used as default location for d365fo.tools. Would you be able to work with us in providing a pull request with the necessary changes to that effect?

[colind-work]

Do you mean implement it myself?

[FH-Inway]

Yes 😄

[colind-work]

I can't give any guarantees, but I will see if it is within my capabilities. If it is within my capabilities, I can't say when it would happen. But we will see.

[FH-Inway]

Sounds good. Let us know in case you need any help.