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

userafBF 0
Viewed 47

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

azure-runbook powershell azure
2 Answers

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 }

enter image description here

userafBF0

You can use the OledbSql PowerShell Module to add in an azure automation for connection.

I have added the module in Azure Runbook. Which follows: enter image description here

Import Module from local

enter image description here

From Gallery enter image description here

enter image description here

Importing the module enter image description here

Delliganesh Sevanesan0
Related