Question 105004 in Azure Question posted in
The official documentation to get you started can be found here.

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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:ModulesUserMicrosoft.AnalysisServices.AdomdClientMicrosoft.AnalysisServices.AdomdClient.dll"
try {Add-Type -Path $assemblyPath}
catch  { $_.Exception.LoaderExceptions }

    enter image description here


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search