topic Re: Refresh power bi report from SSIS in Report Server

Refresh power bi report from SSIS

rdaudt2021 — Mon, 19 Apr 2021 22:58:04 GMT

Hi.

We are working with Power BI Server (on-prem). For this particular report, we are importing data into the pbix file.

Our ETL is done with SSIS. What we want is the Power Bi Report to be refreshed only when ETL completes successfully.

In other words, we would like SSIS to trigger the Power BI report refresh. Has anybody done it?

Thanks for any help with this.

Roque

Re: Refresh power bi report from SSIS

rdaudt2021 — Fri, 23 Apr 2021 03:32:53 GMT

Still looking forward to hear the community's experience with report's refresh triggered by SSIS.

In the meantime, we are playing with the fact that a refresh schedule in Power BI Report Server is implemented by a schedule job in sql server. Based on this, what we have designed so far is as follows:

- in Power BI server, create a scheduled refresh

- find the job in SQL Server, rename it and remove the schedule

- at the end of the ETL job in SSIS, configure a Execute SQL Server Agent Job Task that calls the job renamed above.

Re: Refresh power bi report from SSIS

ForcaTaico — Fri, 23 Apr 2021 06:18:04 GMT

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

Re: Refresh power bi report from SSIS

whereismydata — Mon, 26 Apr 2021 13:41:23 GMT

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

Re: Refresh power bi report from SSIS

rdaudt2021 — Mon, 26 Apr 2021 23:12:19 GMT

Perfect. That's the approach that we will take. Any concern about updates to the PBIX file? Say, if we add a new visual and republish it to Power BI Server, will the same db task still work, refresh the new version of the pbix?

Re: Refresh power bi report from SSIS

whereismydata — Tue, 27 Apr 2021 08:24:26 GMT

Hi, as long as the refresh plan stays the same, there should be no issue. I update my reports regularly and never ran into an issue.

Re: Refresh power bi report from SSIS

kucluk_ok — Mon, 31 Jul 2023 08:44:56 GMT

Hello , Would you mind to share how to use this script? Step by step in SSIS ?

Re: Refresh power bi report from SSIS

ForcaTaico — Tue, 01 Aug 2023 12:17:02 GMT

Hi kucluk_ok

EDIT: Before this works. You must have a refresh plan on the report.

I don't have one at hand. I switched to using a 'Windows Tasks' calling the PowerShell script below.

See this guide How to Schedule PowerShell Script Using Task Scheduler (o365reports.com)
But it could also be a SSIS job (search after PowerShell script in SSIS) or SQL agent job.

PowerShell script:

$path_array =
"report_path1",
"report_path2",
...
"report_pathx"

foreach ($path in $path_array)

{
$uri = "[InsertURL]/reports/api/v2.0/PowerBIReports(path='" + $path + "')/CacheRefreshPlans"
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri $uri

try {
$refreshuri = "[InsertURL]/reports/api/v2.0/CacheRefreshPlans(" + $refreshplan.value.Id + ")/Model.Execute"
}
catch {$error[0]}
Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -method POST -uri $refreshuri -verbose
}

Hope it helps. Regards Taico