Forum Discussion

pallavi_r's avatar
pallavi_r
Icon for Super User rankSuper User
2 years ago
Solved

Refresh power bi dataset from SSIS

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
Need Help
  • ibarrau's avatar
    ibarrau
    2 years ago

    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

ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
2 years ago

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's avatar
    pallavi_r
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      ibarrau
      Icon for Super User rankSuper User
      2 years ago

      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