Download a dataset using SSIS

[JasonY]

Hello, Getting the below error from SSIS while trying to connect to the dataset, from the same machine I can connect o the dataset using the Power BI desktop App, and using DaxStduio.

Also I can connect to SSAS cubes from the same SSIS instance, using the OLEDB for analysis services 13 & 14.  so the issue is when I try to connect to the dataset in PowerBi!

I can connect using my local machine, so it's possible.

TITLE: Connection Manager

------------------------------

Test connection failed because of an error in initializing provider. The following system error occurred: The specified class was not found.

------------------------------
BUTTONS:

OK
------------------------------

[bcdobbs]

Do you have the latest versions of the client libraries:

https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions

worth installing x86 as well as x64 if it will let you. SSIS running locally often gets hung up on needing 32 bit libraries.

[Anonymous]

Thank you @bcdobbs for replying.
I dont see any new providers after installing the client libraries. I might have the latest one already installed then. 

Please let me know if you came accross this scenario of connecting to Power BI Datasets from SSIS?

I am able to connect using SSMS.
I am also able to connect Power BI Desktop underlying tables from SSIS, however not from the Power BI Service datasets.

[bcdobbs]

No direct experience I'm afraid but will certainly try once I get into work. (About an hour)

Suggestion was from prior experience fighting with SSIS on other things.

Possibly unrelated but my work still uses excel 2016 which even with latest libraries hangs when I try and connect to xlma end point via the wizard. Was able to work around that by building a connection file in a more modern version of excel and using that. Will get back to you later this morning.

[bcdobbs]

No afraid I can't make it work either even playing directly with the connection string.

[bcdobbs]

I think this might give you a way forward: 
Solved: How Can I Connect to Premium Model via XMLA Endpoi... - Microsoft Power BI Community

Looks like issue is SSIS can't authenticate on O365 (might be wrong on that) so you need to use a service principal. Setup as follows:

Embed Power BI content in an embedded analytics application with service principal and an applicatio...

[bcdobbs]

Just run through this, setup is straight forward but can't get any further because it appears service principals aren't supported with PPU licences. 

[bcdobbs]

The more the read actually I'm not sure service principals will work here. Would be interested if they do.

[Anonymous]

Hi @bcdobbs 

Thank you for taking time and looking into the issue.

I will try to explore the service principal approach and will update how it goes.

Regards,

Sunil

[Anonymous]

Hi @JasonY ,

This is unexpected. I did a test but failed too. You can file a support ticket here.

Let us know what happens.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @JasonY ,

Based on your description, I think your filename entered in “Server or file name” may be not right. What’s more, this feature of connecting to Power Bi dataset in Service is not yet available for Integration service projects.

But I have found a solution to connect to Power BI if your data model is in Desktop. Please refer to the following idea.

https://www.oraylis.de/blog/how-to-use-power-bi-as-data-source-in-ssis

https://www.powerbi-pro.com/power-query-in-ssis/

It is a great workaround even though there are few limitations to that approach.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JasonY]

....that's why I said it works on my machine so it's possible..

please review: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools

[Anonymous]

Hi @JasonY 
Could you please let me know if you were able to successfully test this connection?
Did the Initial Catalog dropdown populated on its own or you entered the database name manually?

Regards,

Sunil

[JasonY]

No, never been able to automate it; yes, it gets populated on its own.

[Anonymous]

Hi @JasonY,
Thank you for your response.

Could you please let me know if you took a manual approach and if you can share the steps you followed in that manual approach to download data from Power BI Datasets using SSIS?

[BIwoyczi]

Hi @JasonY ,
As @Anonymous, I would highly appreciate a walk-thorugh for how to use the power bi premium XMLA endpoint to connect to SSIS.

I'm not able to achieve it myself... 😕 
Are you using a service principal to do so?

Thanks in advance!