Azure PowerShell Runbook does not support System.Data.OleDb.OleDbConnection

I am trying to open a connection with a ssas server and execute a dax query in order to extract table metadata, through a powershell runbook from an Azure Automation Account.

$daxConnectionString = "Provider=MSOLAP;Data Source=..."

I'm using the following code:

$daxConnectionString = "Provider=MSOLAP;Data Source=$daxServer;Initial Catalog=$daxCatalog;UID=$daxUserId;PWD=$daxPwd"
$daxConnection = New-Object -TypeName System.Data.OleDb.OleDbConnection
$daxConnection.ConnectionString = $daxConnectionString
$daxConnection.Open() 
$daxCommand = $daxConnection.CreateCommand()

The system return the following exception:

System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argument(s): "The .Net Framework Data Providers require Microsoft Data Access Components(MDAC).  Please install Microsoft Data Access Components(MDAC) version 2.6 or later." ---> System.InvalidOperationException: The .Net Framework Data Providers require Microsoft Data Access Components(MDAC).  Please install Microsoft Data Access Components(MDAC) version 2.6 or later. ---> System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {2206CDB2-19C1-11D1-89E0-00C04FD7A829} failed due to the following error: 800736b1 The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail. (Exception from HRESULT: 0x800736B1).

Is there a MDAC module available or can I solve this in an other way?

Thanks in advance, Bart

Solution

To connect to the Analysis server. I'm not using a connection based on the MSOLAP provider but based on the ADOMD Client

daxConnectionString = "Data Source=$daxServer;Initial Catalog=$daxCatalog;User ID=$daxUserId;Password=$daxPwd"
$daxConnection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$daxConnection.ConnectionString = $daxConnectionString
$daxConnection.Open() 
$daxCommand = $daxConnection.CreateCommand()

With this in place the dataset can be filled like:

 $daxAdapter = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $daxCommand
 $daxDataset = New-Object -TypeName System.Data.DataSet
 $daxCommand.CommandText = $query
 $nrRows = $daxAdapter.Fill($daxDataset)

The only thig is that the AdomdClient is not available in the RunBook and also not available in Modules... we create our own...

Download the nuget package from : Nuget.org
Rename the package extension to .zip
Search inside the package for the DLL called Microsoft.AnalysisServices.AdomdClient
Copy the Microsoft.AnalysisServices.AdomdClient.dll file and paste it inside a new folder.
Right-click on the folder and send it to zip.
Add the zip file to the runbook modules by "Adding a module"

Be sure that in the RunBook the library get's loaded by:

$assemblyPath = "C:\Modules\User\Microsoft.AnalysisServices.AdomdClient\Microsoft.AnalysisServices.AdomdClient.dll"
try {Add-Type -Path $assemblyPath}
catch  { $_.Exception.LoaderExceptions }