Update 2021 March:
You can now export the data directly from Power BI Desktop using our free external tool, Power BI Exporter. Read more here.
Update 2019 April:
If you want to export the data model from either Power BI Desktop or Power BI Service to CSV or SQL Server check this out.
In the previous blog posts, I explained how to export Power BI data to Excel and CSV here and here. As promised in this post I explain how to export data from Power BI Desktop to SQL Server.
Hans Peter Pfister has already explained how to export data from Power BI Desktop to SQL Server using R scripts. Although Hans has done a brilliant job, it might be a bit hard to make it work if you don’t have any R experience and you don’t even know how to install and call R libraries. That’s so true about me, I’m NOT an R guy, but, who knows, maybe I will be. 
But, what if you don’t want to go with R? If you are more involved with BI than analytics, then using R might not really be your cup of tea. Luckily, there is another way to export your Power BI data to SQL Server which is more BI friendly. You can export Power BI data to SQL Server using SSIS (SQL Server Integration Services). So if you are familiar with SSIS, then it might be your preferred choice.
With respect to Hans, in this post, I explain his method of exporting data from Power BI Desktop to SQL Server more in detail so that anyone who is not that familiar with R can make it work. I also explain how to export data from Power BI Desktop to SQL Server using SSIS. If there is any other methods you’re aware of please let me know in the comment section below.
Exporting Data from Power BI Desktop to SQL Server with R
As stated before, Hans has already explained this method here. So I don’t explain exactly what he did, but, I use his method to export data from the existing Power BI Desktop model to SQL Server and I explain it step-by-step.
Requirements
To make this method work you need to:
- The latest version of Power BI Desktop, you can download it from here
- Have access to an instance of SQL Server, either on your own machine or on a server in your local network to export the data to
- Either install R for Windows, you can download it from here OR using an existing R-Server OR install SQL Server 2016 R Services
- Install RODBC library for R, you can download the library from here
Note: I haven’t installed R Studio and nothing went wrong.
Installing RODBC Library for R and SQL Server R Services
As mentioned earlier, you can install R OR SQL Server R Services OR R-Server, but, as I haven’t tried R-Server myself I just explain how to install RODBC in R and SQL Server R Services.
You have to download the library from the link provided above, then extract the contents of the zip file which contains an “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder that exists in either R or SQL Server 2016 folders in your “Program Files” folder.
How Does It Work?
Open an existing Power BI Desktop model that you’re willing to export its data to a SQL Server table and follow the steps below: (I use the “Internet Sales” model created on top of AdventureWorksDW. You can download my Power BI Desktop model at the end of this post.)
- Open the Power BI Desktop model
- Click “Edit Queries”
- Right-click on any desired Table you want to export to SQL Server and click “Reference”
- Rename the duplicated table to “Internet Sales Export”
- Now click on “Run R Script” from the “Transform” tab from the ribbon
- Copy and paste the R script below:
library(RODBC)
conn <- odbcDriverConnect("driver=SQL Server; server=SQL_SERVER_INSTANCE; Database=SQLDB")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="TABLE_NAME",rownames=FALSE, safer=FALSE, append=TRUE)
close(conn)- Replace the highlighted parts with your object names then click OK
Note: If you have more than one SQL Server instance then you need to add an extra “\” (backslash) in the server name. The first backslash is considered as an escape character. So your script should look like the below figure.
If everything is fine then you’ll see an empty table in Query Editor without any error messages. Now open SSMS and query the “Exported_From_PowerBI” table to see the exported data.
Note: As you see in the “Run R Script” window, there is a description after the script block showing your current R home directory.
You can change it from Power BI desktop Options as below:
- Click “File” menu
- Click “Options and settings”
- Click “Options”
- Click “R Scripting”
- Select a “Detected R home directory” from the dropdown list then click OK
Note: As you see we’re modifying the query. This is why I created a duplicate from the base query. You may want to put the R script to the base query then remove it after the data is exported to SQL Server.
Hans briefly explained the R script, so I encourage you to have a look at his post to get a better understanding of the R script. For more detailed information check this out.
I tried the above method on a bigger table containing more than 11 million rows and I should say that the performance was not too good. It took more than 15 minutes to export about 1.5 million rows which is not that impressive.
Exporting Power BI Desktop Data to SQL Server Using SSIS
In this method, I connect to Power BI Desktop from SSIS to export data to SQL Server. I explained how to connect to Power BI Desktop more in detail here.
Requirements
- The latest version of Power BI Desktop
- Accessing an instance of SQL Server
- Accessing SSIS (SQL Server Integration Services)
- SSDT (SQL Server Data Tools), you can download here. I use SSDT 2015
How Does It Work?
As stated before we’ll connect to a Power BI Desktop using its random local port from the SSIS package then we export data from the desired table to SQL Server.
Note: Keep in mind that the random port number will change if you close your Power BI Desktop file and reopen it. Therefore, this method is a temporary solution for exporting a larger amount of data from Power BI Desktop to SQL Server.
First of all, we need to find the port number of the local SSAS instance created by the Power BI Desktop. To learn more about the Power BI Desktop port number check this out.
To quickly find the port number:
- Navigate to the directory below:
%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
- There should be an “AnalysisServicesWorkspaceXXX” folder in which XXX is a random number. Open that folder
- Open “Data” folder
- Find “msmdsrv.port.txt” in the folder and open it. It contains the random local SSAS instance port number used by Power BI Desktop
Now we need to create an SSIS project in SSDT.
- Open SSDT
- Create a new “Integration Services” project
- Put a “Data Flow Task” on Control Flow
- In the data flow add an “OLEDB Source”
- Double click OLEDB Source to open “OLEDB Source Editor”
- Click “New” to create a new OLEDB connection manager
- Click “New” one more time
- Set “Provider” to: “Native OLEDB\Microsoft OLEDB Provider for Analysis Services 13.0”
- Set “Location:” to “localhost:XXXXX” which XXXXX is your local Power BI port number
- Select “Initial catalog” from the dropdown list
- Click “Test Connection” to make sure the connection is successful
- You have to click OK several times to get back to “OLEDB Source Editor”
- So far we created a connection manager that you can see in the “OLEDB Source Editor”
- You can see all tables exist in the Power BI Desktop model by clicking on “Name of the table or the view” dropdown list
- As you see there is a bunch of “LocalDate Table” in the model that you cannot see in Power BI Desktop. They are hidden Date tables created by Power BI Desktop automatically to support time intelligence. We are NOT going to select a table from the list as we’ll get an error message at the end.
- Set “Data access mode” to “SQL Command” then write the following simple DAX code in the “SQL command text” box. I know, it says SQL command, but, we put DAX query. Believe me, it works
- Click “Columns” to see the table’s columns then click OK
- Put a “SQL Server Destination” on the data flow
- Connect the “OLE DB Source” to “SQL Server Destination”
- Double click “SQL Server Destination”
- Click “New” to create a new connection manager to our SQL database
- Click “New” again
- The provider should be “Native OLE DB\SQL Server Native Client”
- Enter the “Server name” that you desire to export data to
- Select or enter the database name
- Test connection
- You then need to click OK several times to get back to “SQL Destination Editor”
- Click “New” to create a new table to land our data
- Looking at generated T-SQL you’ll quickly understand that the create table statement won’t work. Copy the code and paste into a text editor OR in SSMS and tidy it up. You may also want to use a meaningful name for the new table.
- Copy/paste the code back to the “Create Table” window then click OK
- Click “Mappings” then map all “Input Columns” to “Destination Columns” then click OK
- That’s it. You just need to run the package now
This method works well even when exporting a larger number of rows.
If you have any other ideas, feedback, etc. I’d love to know about it. So please leave your thoughts in the comment section below.
Download Power BI Desktop model used in this article
Discover more from BI Insight
Subscribe to get the latest posts sent to your email.
Muito bom dia!
Achei incrível seu artigo e me ajudou muito a resolver um enorme problema.
Gostaria de abusar um pouco mais de seus conhecimentos.
Veja! Fiz tudo da forma como você explicou e deu tudo certo, no entanto, quando efetua a consulta no SQL Server algo acontece e, os dados são triplicados. Imagine que no exemplo que fiz, minha tabela original tinha apenas 10 linhas, mas quando faço o procedimento via Power BI, o arquivo da consulta do SQL resulta em 30 linhas.
Muito estranho! Nunca vi isso!
Se puder me ajudar ficarei grato.
E novamente parabéns pelo artivo.
Nossa!
Incrível!
Resolvido!
Muito obrigado pela ajuda e perdoe o incomodo.
Meu Deus!
Tenho um novo problema!
Veja se pode me ajudar, por favor!
Ocorre que, os valores da coluna DATA não aparecem, mas uma mensagem “Microsoft.OleDb.Date” é repetida em todos os registros.
alterar as colunas para usar varchar.
Thank you for share your ideias.
I have a question.
In step five from tutorial: (Set “Location:” to “localhost:XXXXX” which XXXXX is your local Power BI port number), I don’t know where is my port number.
Could you help me?
Hi Felipe,
Welcome to BI Insight.
Perhaps you’re using Power BI Desktop Store edition.
There are several ways to get the port number that I covered lots of them in this article.
The following way will help you finding the local port number with any edition of Power BI Desktop:
1-Open any desired PBIX file in Power BI Desktop
2-Open Windows Task Manager
3-Click “More details”
4-Find “Power BI Desktop” from “Apps” list
5-Expand “Power BI Desktop”
6-Right click “Power BI Desktop” in the lower level and click “Go to details”
7-In the “details” tab you see the PID number for your running instance of Power BI Desktop
8-Open CMD (Command Line) and type in the following command followed by the PID number you got in the previous steps then press enter
netstat /ano | findstr 484
9-The highlighted part in the screenshot below is your Power BI Desktop local port number:
Hope that helps.
Cheers
Hello,
I’m getting that error when running R script:
DataSource.Error: ADO.NET: R script error.
Warning message:
package ‘RODBC’ was built under R version 4.2.0
Warning messages:
1: In odbcDriverConnect(“driver=SQL Server; server=MSSQLSERVER; Database=DataECOM”) :
[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
2: In odbcDriverConnect(“driver=SQL Server; server=MSSQLSERVER; Database=DataECOM”) :
[RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect(“driver=SQL Server; server=MSSQLSERVER; Database=DataECOM”) :
ODBC connection failed
Error in odbcClearError(conn) :
first argument is not an open RODBC channel
Execution halted
Details:
DataSourceKind=R
DataSourcePath=R
Message=R script error.
Warning message:
package ‘RODBC’ was built under R version 4.2.0
Warning messages:
1: In odbcDriverConnect(“driver=SQL Server; server=MSSQLSERVER; Database=DataECOM”) :
[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
2: In odbcDriverConnect(“driver=SQL Server; server=MSSQLSERVER; Database=DataECOM”) :
[RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect(“driver=SQL Server; server=MSSQLSERVER; Database=DataECOM”) :
ODBC connection failed
Error in odbcClearError(conn) :
first argument is not an open RODBC channel
Execution halted
ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Scripting.R.Exceptions.RScriptRuntimeException
Don’t know how to handle it. Any thoughts? Thanks
Hallo Soheil, thank you for the great article.
Could you please help how to get data to SQL from Power BI service, if model is not in local file but is published.
The reason is, that model is periodically refreshed from many sources and we need fresh and right data get back to SQL (local or Azure).
Thank you very much for any help.
Vasek
Hi Vasek.
I wrote another blog post answering your question.
Please check this out:
https://biinsight.com/export-power-bi-service-data-to-sql-server/
Hi Soheil:
When you get data from PBI Service, can you get the output from DAX Measures and Calculated columns?
Great article btw!
Hi there,
Welcome to BIInsight and thanks for your feedback.
That’s a great question indeed.
The answer is no. The reason is that when you connect to a dataset in Power BI Service, you’re connecting live to a model that doesn’t exist in Power BI Desktop. This is also the case when you connect Live from Power BI Desktop to an instance of SSAS.
In the method described in this post, we are treating the Power BI Desktop as an instance of SSAS Tabular. So the principals are similar to when you connect to an SSAS instance. Which means you’d need to connect to the instance, then browse/specify the current catalog (data model).
While the data model resides in another instance there is no catalog to specify. Therefore, you can still connect to the instance, but there is no model fire your queries against.
Hope that helps.
Cheers
HI Soheil:
I checked out both of your blogs and they are too good. Can you please tell me how to connect Power BI service to OLEDB source connector?
Hi myy family member! I want to say that this article iis awesome, great written and come with approximately all important infos.
I’d like to peer extra posts like this .
Hi Soheil,
This article is really awesome, I’m appreciate for that. I have an question:
We have port numbers that are given randomly by Power BI. I guess that we have different random port numbers day by day. How can we optimize connection manager for these port numbers? When we want to import data to Sql Server, we need to build a different connection manager because of random port numbers. Is there any way optimize this connection?
Thanks from Turkey!
Best Regards
Hi Enes,
Welcome to BIInsight.
Re. the port number, Power BI Desktop uses random port numbers whenever you open a PBIX or PBIT file. Which means for the same file you’ll get different port number whenever you close it and open it again.
Read more here.
If finding the port number is a bit of problem for you then you may consider the method below:
Export Power BI Service Data to SQL Server.
Cheers
Hi,
I need to export my PowerBI data to another computer – and with described method I have an error message “The selected data source is on remote computer. The bulk insert operation can be executed only on the computer where this data souce is located.”
Can you help me with sollution?
Thank you in advance Jiri h
Hi Jiri,
Welcome to BIInsight and thanks for your feedback.
Use the SSIS method if possible and should be good to move your data to a remote machine.
If SSIS is not an option then have a look at the method explained here: https://biinsight.com/export-power-bi-service-data-to-sql-server/
It would be great if you can let us know how it goes.
Cheers
Hi
Thanks for the great article. i managed to connect from power bi to sql server using the first method (R) – however , i get this Exception error:
DataFormat.Error: Exception of type ‘Microsoft.Analytics.Modules.R.ErrorHandling.RException.Primitives.NotValidRDataException’ was thrown.
would anyone know why?
try a lower version of R, or try casting the variable that is breaking to a base char/varchar datatype
Is their any way to load this data automatically by scheduling any job? I’m working on exporting the data using SSRS from SQL SERVER database and i can’t do this every month manually. Please do let me know
Hi Sushma
Welcome to BIInsight.com.
If you don’t want to do this process all the time, then I suggest you have a look at this article that I explained how to export Power BI Service data back to an on-prem instance of SQL Server.
https://www.biinsight.com/export-power-bi-service-data-to-sql-server/
You can schedule data refresh that automatically takes care of data movement from the Service to your on-prem instance of SQL Server.
Hope that helps.
Cheers
Thanks for this article! I tried both the method via R and SSIS. The method via R was more successful for me as it at least ran ALMOST without error. The error I did have was with two fields which were date fields and although they were imported, they values only read as “Microsoft.OleDb.Date”. Wondering if you might have any suggestions for these date fields?
But unfortunately for the SSIS method it just did not work. I was successful in setting up the initial source and destination components but again the issue was with the date fields. The execution just would not run as the date fields could not be processed by the task due to DT_DATE conversion issues. I tried to input a conversion task between the source and the destination to convert the date fields but that did not work either. Finally I did remove the two date fields and the execution task at least started: initially it was running without a hitch but then for some unknown reason it failed about 70% of the way (i.e., 30% more records were left to be imported in). SSIS is just so problematic!
Not sure if you can advise on the date fields, will try to research on getting the R method to work without date field import errors at least…
Hi there,
Welcome to BIInsight.com.
I’d like to remind you that this is a temporary solution to export data from Power BI Desktop to SQL Server.
So generally speaking, I’m happy with any methods that works better and easier.
Please check this article for a faster and easier way to export Power BI data to either SQL Server or CSV format.
Hope that helps,
Cheers.
Nice Post, I am trying to import the data from a PowerBi report that was shared to me. I dont have access to their data base. The report contain multiple data sets. This method didnt work for me.
Great article. Very helpful. Is the only way to connect to the Power BI Service through R Script. I’m assuming you can’t do it through your SSIS route? Trying to find the best way for auto refreshed data in Power BI to get into SQL. Thanks!
Hi Ryan,
Welcome to BIInsight.com.
Well, I haven’t tried SSIS to be honest, so it would be great if you can share your findings you tried it.
Cheers.
Great post. When using the R method I am getting the following error message:
DataSource.Error: ADO.NET: A problem occurred while processing your R script.
Here are the technical details:
Running the R script encountered the following error:
Failed to retrieve error code string from System ->15100
Details:
DataSourceKind=R
DataSourcePath=R
Message=A problem occurred while processing your R script.
I don’t know what this error means.
Wao, it’s fantastic, it works 100% for me, but when I try to export a dataset with 300 thousand records it generates the following error …
“ADO.NET: R execution timeout. The script execution was terminated, since it was running for more than 7200000 miliseconds.”
Hi Jhonny,
Welcome to biinsight.com.
I see that you used the “R” method. Have you tried any other methods explained in this blogpost?
In my experience, to export the data into CSV format, the easiest way is to use our free external tool, Power BI Exporter. Learn more about it here.
To easily export the data from Power BI to a SQL Server database, then the easiest way is to use Dax Studio. Read more here.
Cheers
Hi.
Thanks a lot for your articles!
I have a question is there any way to get power bi service data (for example list of workspaces) to sql table?
Is there any way to call power bi admin api and put result into sql table (not to use azure data factory) ?
Thanks for this posting, do you know if there is a process to move the data set into azure sql database
Hi Gabriel,
The method I explained here works fine using DAX Studio. Ensure you use SQL Authentication (I did not test DAX Studio with MFA and Azure Authentication) and whitelist your IP address on the Server Firewall.
Cheers
Thanks for posting..
My requirement: Power BI data transfer to SQL table
• I have Power Bi Report in Desktop that is fetch data from one of the data source (SAP BW).
• I can transfer Power data from Your given above solution using DAX studio
o But is there any limit of data transfer like I found 1M records at a time.
o I have 3M+ records in one table in power bi that need to transfer.
• +I want when the data transferred from power bi to SQL using your given solution using DAX studio then How this process of transferring will be automated, So When new data comes in power bi table this DAX studio solution should be automatic transfer my data to SQL table going forward.
Manish Beniwal
Soheil,
I downloaded your Export Power BI Desktop to SQL Server with R modify connection string point to my SQL Server AdventureWorksDW2019 everything went well but every time I refresh the data it insert 3 times
They are 60,398 records in FactInternetSales and 181194 in Exported from PowerBI2 table? any idea by the way I folow BI Elite export to SQL and also it insert 3 times?
Thanks,
Oded Dror
Hi Oded,
Thanks for your comment.
Please note that this post is pretty old (July 2017). Back then, there weren’t many options to achieve the scenarios discussed in this post.
I would recommend using one of the updated methods mentioned at the beginning of this post.
Thanks
Soheil,
Thank for the response, I was looking for writing back to SQL Server on refresh, but I found the solution.
Thanks Again
Oded Dror
I’m glad to hear that. It would be great if you can share your findings with other folks.
Cheers
Soheil,
Here is the link.
https://www.youtube.com/watch?v=WgIhsF7kEjI&ab_channel=BIElite
Thank you Oded for sharing.
The video shared a bright idea indeed.