How to run unit tests with embedded drivers

[stevelorddremio]

I'm finding the new 2022 Power Query SDK is giving benefits already. I can set credentials using username/password, however, how do I run unit tests when my custom connector is using an ODBC driver embedded within Power BI? Within the VS Code environment the Power BI driver is not visible.

[mattmasson]

Hi Steve,

Thanks - I've been meaning to post these docs. Since it only impacts a small number of connectors, it likely won't be part of our public documentation, but I think it makes sense to post it here.

Testing ODBC based connectors with embedded drivers

If you receive a DataSource.MissingClientLibrary error when you run an evaluation, and your connector sets UseEmbeddedDriver=true, you will need to perform some manual steps to test your connector.

The SdkTools nuget package does not contain the ODBC drivers that are bundled with Power BI. To test connectors with embedded drivers, you need to copy (or link) the ODBC Drivers directory from a Power BI Desktop or Gateway install to your local SdkTools directory.

Steps to create ODBC Drivers directory

Note: you will need to create the ODBC Drivers every time a new version of the PowerQuery.SdkTools nuget package is released.

Locate Power BI Desktop (MSI) or On-Prem Data Gateway bin directory

Install PBI Desktop MSI or Gateway and locate the Mashup Engine binaries (i.e. Microsoft.Mashup.Container.exe).

The default location for Power BI Desktop will be: %ProgramFiles%\Microsoft Power BI Desktop\bin

The default location for Gateway will be: %ProgramFiles%\On-premises data gateway\m

This directory should have an ODBC Drivers subdirectory, containing all of the drivers we embed with the product.

Locate Power Query SDK for VS Code install directory

Install the Power Query SDK for VS Code and use it to create a new connector project or open an existing project. On first use, the SDK will download the latest Microsoft.PowerQuery.SdkTool nuget to the extension's cache folder.

The default location of the extension folder will be

%UserProfile%\.vscode\extensions\powerquery.vscode-powerquery-sdk-x.x.x-win32-x64

Where x.x.x is the current version of Power Query SDK extension.

The SdkTools nuget package is found under:

<extension path>\.nuget\Microsoft.PowerQuery.SdkTools.2.xxx.x\tools

If you do not have a .nuget folder, be sure to load a connector workspace in VS Code to trigger the SDK's auto-download mechanism.

Create symbolic link to ODBC Drivers directory

Open a windows command prompt and use the mklink command to create a symlink in the SdkTools directory that points to the ODBC Drivers directory in the Power BI Desktop or Gateway mashup directory.

mklink /D "<sdk tools directory>\ODBC Drivers" "<mashup directory>\ODBC Drivers"

Example:

mklink /D "%UserProfile%\.vscode\extensions\powerquery.vscode-powerquery-sdk-0.1.7-win32-x64\.nuget\Microsoft.PowerQuery.SdkTools.2.110.3\tools\ODBC Drivers" "%ProgramFiles%\Microsoft Power BI Desktop\bin\ODBC Drivers"

Alternatives to creating a symlink

• Copy the entire ODBC Drivers folder to the tools directory
• Copy only the driver you need (.ini file + directory)
• Manually install the Microsoft.PowerQuery.SdkTools nuget to another directory and point the powerquery.sdk.tools.location setting in VS Code to it. This avoids the need to recreate the ODBC Drivers directory on each update of the SdkTools, but might put your tools out of sync with the SDK extension.

[stevelorddremio]

Thanks Matt. Excellent instructions. Worked first time.

[mattmasson]

Please note that the paths will be changing in an upcoming release of the SdkTools package. If there is a Mashup directory under tools, then the ODBC Drivers directory should be placed in tools\Mashup\ODBC Drivers.

I'll update these instructions once we have a release version.