Copy Power BI data mart tables to onprem SQL server

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?