question

FabioBeka-5792 avatar image
0 Votes"
FabioBeka-5792 asked FabioBeka-5792 commented

How to execute a DMV query in SSAS through a powershell runbook from Azure Automation?

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

I have already written and tested a powershell script that seems to work fine on my local machine, but when I run the same script on the cloud as an azure runbook it seems impossible to open a connection.

I used this code to open the connection with ssas:

 $connectionString = "Provider=msolap;Data Source=asazure://westeurope.asazure.windows.net/servername;User Id={0};Password={1}; Initial Catalog=DataModel" -f $ssasUser, $ssasPassword;
    
 ## Connect to the data source and open SSAS
 $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
 $connection.Open()

I can't seem to be able to open a connection, as I run this code I receive the following error:

 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."


Searching on the web, the only solution I found seems to be to simply download and install the MDAC sdk but this can't be done while running on the cloud.

I need help to fix this error or find an alternative solution.
Thank you.

windows-server-powershellsql-server-analysis-servicesazure-automationazure-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello FabioBeka-5792,

Thanks for posting your questions in Microsoft Q&A.

According to the connection string, data source is "asazure://westeurope.asazure.windows.net/servername", we can know it is an Azure analysis server, in order to get more professional support, I have added the correct tag "Azure-analysis-services". Thanks for your understanding and support.

0 Votes 0 ·

1 Answer

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered FabioBeka-5792 commented

This script is aim to SSAS on-prem? It seems this need to be fixed from Azure automation side. It seems that not much can be done from SSAS or PowerShell perspectives.
You could try get help from Azure side to see if any other users have done such job. And I would suggest to start fixing and Automation settings and the $connection.Open() function.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In more detail, what exactly should I be looking to fix in the Automation settings and $connection.Open() function?

0 Votes 0 ·

Just from the error message, I am guessing in the azure environment we need to fix this. But I am not familiar with azure automation, for this case we need to check with azure expertises.

0 Votes 0 ·

Okay, I understand.
Thank you very much.

0 Votes 0 ·