Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rdaudt2021
Helper I
Helper I

Refresh power bi report from SSIS

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

2 ACCEPTED SOLUTIONS

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

View solution in original post

whereismydata
Resolver IV
Resolver IV

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

 

View solution in original post

7 REPLIES 7
whereismydata
Resolver IV
Resolver IV

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

 

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?

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.

rdaudt2021
Helper I
Helper I

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.

 

 

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors