cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rdaudt2021
Frequent Visitor

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 II
Resolver II

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

5 REPLIES 5
whereismydata
Resolver II
Resolver II

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

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
Frequent Visitor

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors