Copy Power BI data mart tables to onprem SQL server

[BIDataDeveloper]

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?

[tbucki1]

@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.

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...

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

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

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

Then Map data columns...

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

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

Confirm everything looks good....

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

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...

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

Conversion over passes.... 🙂

Now the import....

I select SSIS...

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

Confirm everything....

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

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?

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]

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

[otravers]

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:

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.