Automate Power Query Refresh and Import into SQL Server

[hipau]

Hi,

I'm looking for some advice on how to best automate this weekly process:

1. Open numerous .xlsx files that use Power Query to connect to Microsoft Dynamics.
          - To elebroate, the .xlsx Power Query uses Source = OData.Feed to connect to Microsoft Dynamics

2. Select Refresh All. Save and close.

3. Open SQL Server Management Studio and use the import wizard to copy data & delete rows in destination table (not append rows).
4. Open the .pbix dashboard, which connect to the Microsoft SQL Server Tables and select Referesh All.

          - It may be possible to streamline the process by having .pbix files point to the .xlsx fles, bypassing SQL Server Management Studio, but there are a number of transformations that are more efficient in SQL Server, which makes this an unfavourable option.

Apologies if this is in the wrong section, or if i need to split this up into a few posts. 

Regards, hipau

[Greg_Deckler · Accepted solution]

SSIS or Azure Data Factory

[hipau]

Thanks for your response. I'll take a look into SSIS as a starting point. Is there any particular reason you would choose one over the other?