Hey @mj2024,
I would prefer the Power BI Service approach. You can simply publish, schedule refresh in browser, and point at local SQL Server using a Data Gateway, all in just a few clicks.
That said, a Powershell script also works like a charm. The script below performs the job as follows:
- Closes any open PowerBI instances.
- Waits for the report to open completely (increase the wait time if needed).
- Dismisses the startup splash screen.
- Triggers refresh using keyboard shortcuts (Alt, H, R, Down Arrow, Enter)
- Waits for the refresh (increase the wait time if it takes longer for you)
- Saves the changes.
- Closes the report.
- Logs all actions to the file specified in the script.
param(
[string]$PBIXPath = "C:\Users\GuptaHarshit\Downloads\SampReport.pbix",
[int]$LoadWaitSec = 240,
[int]$RefreshWaitSec = 60,
[string]$LogFile = "C:\Users\GuptaHarshit\Downloads\RefreshLog.txt"
)
function Write-Log {
param([string]$Message)
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$line = "$timestamp $Message"
Write-Host $line
Add-Content -Path $LogFile -Value $line
}
Add-Type @"
using System;
using System.Runtime.InteropServices;
public class WinAPI {
[DllImport("user32.dll")] public static extern bool SetForegroundWindow(IntPtr h);
[DllImport("user32.dll")] public static extern bool ShowWindow(IntPtr h, int n);
}
"@
Add-Type -AssemblyName System.Windows.Forms
Write-Log "Checking for existing Power BI Desktop instances..."
$existing = Get-Process -Name "PBIDesktop" -ErrorAction SilentlyContinue
if ($existing) {
Write-Log "Found existing instance - closing it first..."
Stop-Process -Name "PBIDesktop" -Force
Start-Sleep -Seconds 5
}
Write-Log "Launching Power BI Desktop with: $PBIXPath"
$pbiExe = "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe"
if (-not (Test-Path $pbiExe)) {
$pbiExe = "C:\Program Files (x86)\Microsoft Power BI Desktop\bin\PBIDesktop.exe"
}
if (-not (Test-Path $pbiExe)) {
Write-Log "ERROR: Power BI Desktop executable not found."
Exit 1
}
Start-Process -FilePath $pbiExe -ArgumentList $PBIXPath
Write-Log "Waiting $LoadWaitSec seconds for Power BI to fully load..."
Start-Sleep -Seconds $LoadWaitSec
$pbi = Get-Process -Name "PBIDesktop" -ErrorAction SilentlyContinue
if (-not $pbi) {
Write-Log "ERROR: Power BI Desktop did not start."
Exit 1
}
[WinAPI]::ShowWindow($pbi.MainWindowHandle, 9)
[WinAPI]::SetForegroundWindow($pbi.MainWindowHandle)
Start-Sleep -Seconds 3
# Dismiss the splash/sign-in popup by pressing Escape
Write-Log "Dismissing startup splash screen..."
[System.Windows.Forms.SendKeys]::SendWait("{ESC}")
Start-Sleep -Seconds 3
# Click the main Power BI window to make sure it has focus
[WinAPI]::SetForegroundWindow($pbi.MainWindowHandle)
Start-Sleep -Seconds 2
# Trigger refresh via ribbon: Alt, H, R, Down Arrow, Enter
Write-Log "Triggering refresh via ribbon..."
[System.Windows.Forms.SendKeys]::SendWait("%")
Start-Sleep -Seconds 2
[System.Windows.Forms.SendKeys]::SendWait("h")
Start-Sleep -Seconds 2
[System.Windows.Forms.SendKeys]::SendWait("r")
Start-Sleep -Seconds 2
[System.Windows.Forms.SendKeys]::SendWait("{DOWN}")
Start-Sleep -Seconds 1
[System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
Write-Log "Waiting $RefreshWaitSec seconds for refresh to complete..."
Start-Sleep -Seconds $RefreshWaitSec
Write-Log "Saving the file (Ctrl+S)..."
[System.Windows.Forms.SendKeys]::SendWait("^s")
Start-Sleep -Seconds 10
Write-Log "Closing Power BI Desktop..."
Stop-Process -Name "PBIDesktop" -Force
Start-Sleep -Seconds 3
Write-Log "SUCCESS: Refresh complete."
Exit 0
For SSIS:
Update the PBIXPath and LogFile parameters in the script as per your env.
Add the script task to the SSIS Control Flow and configure accordingly.
Hope it helps!
Best,
Harshit
