Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
SSIS or Azure Data Factory
SSIS or Azure Data Factory
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?