How Can I Connect to Premium Model via XMLA Endpoint from SSIS on-prem?

[AnonymousPerson]

Hello,

Can anoyone smarter than me please let me know if and when it may be possible to connect to the Power BI Premium XMLA Read/Write endpoint from either SSIS or SQL Agent on-premise?  I'd like to connect and issue a query to kick off processing of a single table on its own schedule.

This post seems to suggest this should be possible. But I cannot get it to authenticate properly in my environment (I do have a working service principal acct)...

Announcing support for service principals using the XMLA endpoint in Power BI Premium | Microsoft Po...

If this is not supported, is it at least on the roadmap? Without support for SQL Agent or SSIS to actually automate stuff, the XMLA endpoint is worthless except for limited testing / config. 

(Any answer that contains "PowerShell" or "REST API" is automatically wrong 😁).

Thanks!

[AnonymousPerson · Accepted solution]

Just for posterity, this is now possible since XMLA endpoint is GA.  

Troubleshoot XMLA endpoint connectivity in Power BI - Power BI | Microsoft Docs

Connecting with a service principal
If you've enabled tenant settings to allow service principals to use Power BI APIs, as described in Enable service principals, you can connect to an XMLA endpoint by using a service principal. Keep in mind the service principal requires the same level of access permissions at the workspace or dataset level as regular users.

To use a service principal, be sure to specify the application identity information in the connection string as:

User ID=<app:appid@tenantid>
Password=<application secret>
For example:

Data Source=powerbi://api.powerbi.com/v1.0/myorg/Contoso;Initial Catalog=PowerBI_Dataset;User ID=app:91ab91bb-6b32-4f6d-8bbc-97a0f9f8906b@19373176-316e-4dc7-834c-328902628ad4;Password=6drX...;

If you receive the following error:

"We cannot connect to the dataset due to incomplete account information. For service principals, make sure you specify the tenant ID together with the app ID using the format app:<appId>@<tenantId>, then try again."

Make sure you specify the tenant ID together with the app ID using the correct format.

It's also valid to specify the app ID without the tenant ID. However, in this case, you must replace the myorg alias in the data source URL with the actual tenant ID. Power BI can then locate the service principal in the correct tenant. But, as a best practice, use the myorg alias and specify the tenant ID together with the app ID in the User ID parameter.

Also might not hurt to update your MSOLAP drivers for SSIS:

Analysis Services client libraries | Microsoft Docs

[Greg_Deckler]

@AnonymousPerson As of June it doesn't look like this is available:

Solved: connect to powerbi dataset in service from ssis - Microsoft Power BI Community

And here is one from 7 days ago that basically states the same:

Solved: How connect ssis with a dataset premium to process... - Microsoft Power BI Community