How to use SSIS with Power Query to extract data from Power BI Premium and insert into local CSV
I was helping and answering some questions on the Power BI community forums and I came across a question where someone was looking to query data from Power BI Premium using SSIS (SQL Server Integration Services).
This is definitely possible and I will show you below how I can use SSIS, to query data from Power BI Premium and store it in a local CSV
Pre-requisites
In order to get this completed I had to have either SQL Server 2017 or SQL Server 2019 with SSIS installed. (If this is not installed, the Power Query Source will not install)
I went to my Power BI Premium App Workspace and went into the setting for the App and made a note of the Workspace Connection. My example below it is: powerbi://api.powerbi.com/v1.0/myorg/Hours Worked
Example
In my working example I am going to connect to my Power BI Premium dataset.
I will then run a simple DAX query which will return the Sales by Date.
Finally, I will then extract this data into a CSV File.
Installation and configuration of the Power Query Source
On the server where I have got SQL Server (2017,2019) installed is where I will download and install the Power Query Source.
- Here is the location where I downloaded the Microsoft SQL Server Integration Services Connector for Power Query (Preview)
- NOTE: When using SSIS and SQL Server SSIS I had to download and install both the x86 and x64 versions
-
As with my example I selected the 2019 version
-
Once downloaded I then installed the Power Query Source for both x86 and x64
-
Once completed it said it was successfully installed
Creating the Power Query M Code to be used in SSIS
The Power Query Source in SSIS only uses the M Code, which is found in Power Query Editor.
As with most things there is often an easier way to do this. In my working example I do this by using Power BI Desktop with the Power Query Editor to generate the M code.
- I opened Power BI Desktop and went into Power Query
-
Next, I clicked on Get Data and selected SQL Server Analysis Services database
- Please note that this will work for Power BI Premium too!
-
I then put in the Workspace Connection and the dataset name (Which is known asa the Database)
- I then changed the data to be import.
-
And then I put in the following DAX to extract the sales by date
-
The next step was for me to authenticate and depending I do not have MFA enabled on this account, so I put in my details under Basic settings
- I then loaded the data and changed the data types.
-
I could then see the following in my Power Query Editor
- I then went into the Advanced Editor and copied all the M Code into Notepad
-
CODE
let
Source = AnalysisServices.Database(“powerbi://api.powerbi.com/v1.0/myorg/Hours Worked”, “WWI Sales – Incremental Refresh”, [Implementation=”2.0″, Query=”EVALUATE#(lf)#(lf)SUMMARIZECOLUMNS(#(lf)#(tab)’Date'[Date],#(lf)#(tab)””Total Sales””,[Sales]#(lf)#(tab))”]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date[Date]”, type date}, {“[Total Sales]”, Currency.Type}})
in
#”Changed Type”
- I now have my Power Query M code, with the DAX query ready for SSIS
Creating the SSIS Dataflow task to connect to and extract data from Power BI Premium
In the steps below I then create the SSIS dataflow task, query the data from the Power Query Source and then extract the data into a CSV
- I created a new SSIS project, and went into my Package.dtsx
-
Next, I added a dataflow task
-
Once in the dataflow task I then dragged in Power Query Source from the Common section
-
I then opened the Power Query Source and pasted in my M Code from the earlier steps
-
I then click on Connection Managers
- Then I clicked on Detect Data Source
-
It takes a few seconds and I then got a confirmation that it detected a data source
- I could then see the Name and Description in the Power Query Connection Manager.
-
As shown above the Connection Manager is blank.
- I then clicked on the Connection Manager and clicked on New Connection
- This already completes the Data Source Kind, Data Source Path, I now need to confuigure how I am going to connect and I change it from Windows Authentication to Username Password.
- I then put in my username and password
-
I then clicked on Test Connection to make sure it works. It does take a few seconds to complete.
- I then clicked Ok twice to get back to the Power Query Source Editor
-
My final step was to click on Columns to view the columns that are returned (Once again this will take a few seconds to complete)
-
I then clicked Ok
-
My next step was to add in my Flat File Destination and configure it
- Once configured I can then see my columns mapped through
- I then click on the Mappings and map the columns
- I then clicked Ok (Once again this will take a few seconds to complete)
-
Now, I tested that this would run successfully in SSIS by running the dataflow task
-
And to make sure that the data has been extract as expected here is what the CSV looks like when opened
- I have successfully got the data extracted.
Summary
As I have shown in this blog post I have demonstrated how to connect to Power BI Premium and extract data using the Power Query Source and SSIS.
This will allow me to extract the data and insert it into almost any other data source where the data can then be hosted.
Any questions or comments are most welcome. Thanks for reading!!
Hi
I keep getting the below error when trying to bring in a query from the dataset into Power BI
The column ‘[ColumnName].[Key].[Key].[MEMBER_UNIQUE_NAME]’ of the table wasn’t found.
Hi Taariq,
Thanks for the comment.
It would appear that you are trying to do an MDX query and not a DAX query?
Hi,
Interesting article.
Does this work also with PowerBI Pro instead of premium? can it be used to get data from a dataset held in powerBI Services?
Thanks in advanced
Hi Marcus, thanks for the comment.
Unfortunately this can only be done when you can connect via the XMLA end point, which applies to Power BI Premium or Premium Per User.
Hi Gilbert,
I followed this guide but I am getting an error = ‘Connection string is not valid’ when using my username and password. I can connect to the dataset using these credential in SSMS though. Any ideas?
Thanks
Hi Alex
Just to confirm that you do NOT have Multi Factor on your account being used?
If you do have Multi Factor enabled it will not work because it does not prompt you for the MFA code.