Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BIDataDeveloper
New Member

Copy Power BI data mart tables to onprem SQL server

I created a data mart within our power BI server space. I love the ability to leverage Power Query to develop tables needed for reporting purposes. As you know when the data mart is created in power, BI an azure SQL database is also created on the backend. I am able to connect to this database with SSMS...my question is how do I automate a copy paste table process from the data mart into an onprem server database that I already have? Basically I want to use PowerQuery/Datamarts for pulling all my data tables together then I want to load them all to another server/database. How do I do this?

 

Also, how do I set up new users via SMSS for my Power bi datamart so that I can add a Service Account for this datamart allowing me to connect into it with other tools like ADF and Visual Studio for building Analysis Service Tabular data models?

3 REPLIES 3
tbucki1
Advocate I
Advocate I

@otravers  I looked at your comments and tried the following but am still facing an issue with connection into the Azure SQL DB... FYI below....

 

In SSMS I AM able to connect to both my OnPrem and Azure Datamart SQL DB .... and then go to Tasks > Export Data.

 

tbucki1_0-1680529885248.png

 

After I hit Export data I am asked to plug in all the details of my source data... which IS the Power BI Datamart SQL.... Auth via Active Directory with MFA...

tbucki1_1-1680530070303.png

 

Confirmed I can connect to the Power BI Azure DB...

 

tbucki1_2-1680530132249.png

 

Then I press OK and am asked for the OnPrem Destination info... Same thing, Confirmed I can connect.

 

tbucki1_3-1680530189264.png

 

After OK I select "Copy data...."

tbucki1_4-1680530223553.png

 

Then Map data columns...

tbucki1_5-1680530247681.png

 

I then choose Run Immeditally AND Save SSIS because I want to use this DTSX package to run every morning automatically...

 

tbucki1_6-1680530292936.png

 

I create a SSIS file as "SSIS - Test" and save it to C:\

tbucki1_7-1680530356547.png

 

Confirm everything looks good....

tbucki1_8-1680530381943.png

 

SUCCESS!... Copy from Matamart to OnPrem works, BUT this was manual, and now I want to automate this process....

tbucki1_9-1680530398860.png

 

Now I want to take that SSIS and set it up to run automatically.... This is where everything breaks....

Now I want to create an SSIS job so I go to Integration Services Catalog and load the .dtsx file...

 

tbucki1_10-1680530597714.png

 

Select the file, load it, and review everything....

tbucki1_11-1680530662130.png

 

Conversion over passes.... 🙂

 

tbucki1_12-1680530781026.png

 

Now the import....

tbucki1_13-1680530798619.png

 

I select SSIS...

tbucki1_14-1680530816382.png

 

I select the OnPrem server, choose Windows Auth, and add the SSIS Path on the server.

 

tbucki1_15-1680531887163.png

 

Confirm everything....

tbucki1_16-1680531945207.png

 

Deploy to Server... and everything loads perfectly...

tbucki1_17-1680531979741.png

 

Now I go to the SSIS-Test.dtsx on the server and run execute.... BUT NOW IT FAILS!?!? Seems like a connection issue, but earlier I was able to auth in no problem?? What's wrong here?

 

tbucki1_18-1680532052004.png

 

tbucki1_19-1680532109873.png

 

Why is this now failing when I run the .dtsx from the server, but earlier when I ran it manually everything copied perfectly??

 

What and I doing wrong, and how to fix it....

 

Also if there is a better way to automate let me know.... Long story short here is what I want.

 

I want to use Power Query to prep/tranform, and format my fact and dimension tables, then I want to take that formatted output table and load it to an onprem server database table.... How best to do this? Currently above I am trying to make use to Power BI datamart and SSIS to copy tables over, but would this be easier with Power BI Data Flow instead? Is there another way to easily accomplish this? I love Power Query want to use this and not have to build some complex SSIS Data Flow with Code. Also my Power Query is complex as the output tables are built off multiple prequeries upstream...

 

Thoughts?

otravers
Community Champion
Community Champion

Sorry I have no idea why your SSIS process fails, I don't use SSIS myself.

 

If you want to use a Power BI dataflow so that you benefit from Power Query Online, you'll have to enable "Bring Your Own Storage" so that the underlying Azure Data Lake Storage service is exposed to you. You'll then need to orchestrate the data out of your datalake into your on premises SQL:

https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-azure-data-lake-stora...

 

You could also run dataflows in PowerApps and save the data in Dataverse, then ETL from Dataverse to SQL:

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-and-use-dataflows

 

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
otravers
Community Champion
Community Champion

I just tried connecting to the datamart's SQL endpoint in SSMS's SQL Server Import and Export Wizard and was able to copy a table to an on-premises server. At the end of the wizard you can define an SSIS package for re-use. I used the Microsoft OLE DB Driver for SQL Server with AD - MFA authentication:

 

otravers_1-1680444300787.png

 

See:

https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

 

You could try connecting with SQL Server Agent and report here whether it also works.

 

As far as I know you can't create SQL users in the datamart bypassing Power BI's user model. You could try creating a user principal in AAD that has access to the datamart and see if that works. 

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors