Refresh power bi dataset from SSIS

[pallavi_r]

Dear Team,

Is there a way I could trigger the power bi dataset refresh from SSIS. We are using on-premises sqlserver, no Azure.

Kindly advise if anyone has done it.

Thanks,

Pallavi

[ibarrau]

Hi. Are you talking about Power Bi Report Server? I know you can use SQL Agent like: https://learn.microsoft.com/en-us/power-bi/report-server/scheduled-refresh

If you have Power Bi Service with on premise data you can trigger the refresh from SSIS running an API Request like: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset-in-group

Those are your alternatives. PowerBi Report Server API doesn't have a request for refresh: https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0

Maybe there is a way to interact between SSIS and SQL Agent, I'm not sure about that one.

I hope that helps,

[pallavi_r]

Hi @ibarrau 

Thanks for you reply. We have on-premises sql server, ssis that needs to refresh dataset from power bi service.

Can we do it using sql server agent and powershell. Kindly check and let me know if possible.

Thanks,

Pallavi

[ibarrau · Accepted solution]

I'm nost sure about sql server agent. What I can say here is that you can write a powershell code that would run a request with PowerBi cmdlets or in general using the PowerBi Rest API in order to refresh a dataset. Once you have your powershell script then you can investigate if you can run it from SSIS, SQL Agent, etc. I'm not a SSIS expert but it should have a way to run a rest api request.

I hope that make sense