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

3 Replies

  • 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