Refresh power bi report from SSIS

Hi,

One option is to use execute a PowerShell script in SSIS.

Credit to Cataster on stackoverflow: powershell - How to refresh a PowerBI Report? - Stack Overflow

# Retrieve the scheduled refresh data for a specific report and set it to a variable to get the Id value
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri "[ReportServerURL]/reports/api/v2.0/PowerBIReports(path='[REPORTPATH]')/CacheRefreshPlans" 

# This is how you can reference the Id value in the results from above
$refreshplan.value.Id
$refreshplan.value.LastRunTime
$refreshplan.value.LastStatus

# Using the above Id value, create the URI string to run the Model.Execute method
try {
    $refreshuri = "[ReportServerURL]/reports/api/v2.0/CacheRefreshPlans(" + $refreshplan.value.Id + ")/Model.Execute"
}
catch {$error[0]}

# Invoke the Model.Execute method to start the scheduled refresh for the PBIX report
Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -method POST -uri $refreshuri -verbose

# To check on the scheduled refresh status, you can update the data in $refreshplan by running the CacheRefreshPlans again
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri "[ReportServerURL]/api/v2.0/PowerBIReports(path='[REPORTPATH]')/CacheRefreshPlans"

# Get the LastRuntime and LastStatus values to check the status
$refreshplan.value.LastRunTime
$refreshplan.value.LastStatus

Regards Taico

Hi,

if you already created a refresh plan, then you can do this:
First, create a package parameter. Then create a package and insert a execute sql task. Execute this statement:

  exec [pbiReportServer].dbo.AddEvent @EventType='DataModelRefresh', @EventData=?

In parameter mapping map your package paramete and deploy. 

Now you can add this package to all your jobs. Simply put your subscription ID in the parameter and execute.

If you need the ID, heres a SQL script to get it:

SELECT [SubscriptionID]
	,c.Name AS ReportName
FROM [dbo].[ReportSchedule] r
LEFT JOIN [dbo].[Catalog] c ON r.ReportID = c.ItemID
WHERE c.Name = 'YOUR REPORTNAME'

Hope this helps