How to refresh Power BI dataset with REST API using SSIS

In this article, you will learn how to refresh a Power BI dataset with Power BI REST API using SSIS and ZappySys SSIS PowerPack. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Each Power BI report contains a dataset, which can be refreshed to show relevant data in the report. Power BI also has a Scheduled refresh capability, in case you want to refresh a dataset periodically. However, you are limited to only 8 specific times per day you can refresh a dataset (as of May 23, 2019). In the Power BI Premium license you can do it 48 times per day. But what if you need to refresh it more frequently? Or if you desire to refresh it at a non-predefined time; which is not known in advance? E.g. after an ETL process has finished? Here Power BI REST API comes into rescue and lets you refresh a dataset on demand.

Here are a few use cases when you may want to refresh a dataset from a SSIS package, using Power BI REST API:

The need to refresh a dataset more often than Power BI allows.
The requirement to refresh a dataset just after some process is finished; e.g. after an ETL process.
The ability to have absolute control when to refresh multiple datasets. Sometimes, refreshing multiple huge datasets at once could cause memory problems in the Power BI service. So, having an orchestrated refresh would solve this problem.

We will use these ZappySys SSIS PowerPack connectors/connections in this article:

OAuth Connection Manager

REST API Task

Let’s begin!

Prerequisites

You have a Power BI account and you have basic Power BI skills.
An uploaded Power BI report to Power BI service.
A Microsoft Azure subscription (there is a free option with a 200 USD credit).
Azure Active Directory set up.
SSIS designer installed. Sometimes it is referred to as SSDT or BIDS (download it from the Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
ZappySys SSIS PowerPack is installed (download it).

Getting started

Suppose you have a simple report and you want to refresh its dataset using Power BI REST API and SSIS. So far, the dataset has not been refreshed at all:

Preparing to refresh Power BI dataset using SSIS and Power BI REST API.

Preparing to refresh the Power BI dataset using SSIS and Power BI REST API.

To start, create an SSIS package and follow the steps below to accomplish the goal.

Step By Step – How to refresh a Power BI dataset with REST API using SSIS

Create an OAuth application

Create an Azure Active Directory OAuth application

Firstly, go to Azure Active Directory, click App Registrations, and then register a new application: Select option Web and enter this URL into the Redirect URI field: https://login.microsoftonline.com/common/oauth2/nativeclient
Once created, copy/paste the Application (client) ID into a notepad, we will need it later:
Next, go to Certificates & secrets and create a new client secret. Copy a new client secret, we will need it later:
Then go to Overview and hit Endpoints. Proceed by copying the Authorization and Token URLs and paste them into a notepad; we will use them later:

Create OAuth Connection Manager in SSIS package

Drag and drop REST API Task from SSIS Toolbox, double click, select Url from connection, and then select <New ZS-OAUTH Connection> item:
Creating OAuth Connection Manager to connect to Power BI OAuth Application from SSIS.
Input Client ID from Step 2 when we were creating an Azure Active Directory OAuth Application.

Configuring OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.
Then proceed and input the Client Secret from Step 3, Create an Azure Active Directory OAuth application section.
Please go on, and fill in Authorization Url and Access Token Url properties with values we copied in Step 4, Create an Azure Active Directory OAuth application section.
In Scopes / Permissions field input these scopes:
https://analysis.windows.net/powerbi/api/Dataset.ReadWrite.All offline_access
Then go to the Advanced tab and in the Callback/Return Url field input this value:
https://login.microsoftonline.com/common/oauth2/nativeclient
Setting Callback/Return Url (Redirect URI) in OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.
Then go back to the tab General and click the Generate Token button.
Once asked for credentials, log in with your organization user; this will generate Refresh and Access tokens.

Retrieve the ID of the Power BI dataset you want to refresh

Go to Power BI datasets.
Then click on Datasets.
Proceed and click the dataset you want to refresh.
Finally, copy the ID of the dataset from the URL, we will use it in the next section.

Getting Power BI dataset ID to refresh it in an HTTP request to Power BI REST API.

Configure REST API Task to refresh a Power BI dataset

We configured OAuth Connection Manager and retrieved the ID of the dataset we want to refresh, now it’s time to configure REST API Task and refresh the dataset. So do the following:

Go to the SSIS package and open the REST API Task for configuration.
Input URL in such a format:
https://api.powerbi.com/v1.0/myorg/datasets/DATASET-ID/refreshes Replace DATASET-ID with an actual ID of the dataset. E.g.:
https://api.powerbi.com/v1.0/myorg/datasets/a38c78fc-8c87-45e3-b961-6dcd3ac1eada/refreshes
NOTE: If your dataset resides in a Workspace, use this format instead:
https://api.powerbi.com/v1.0/WORKSPACE-ID/datasets/DATASET-ID/refreshes
Then set POST as HTTP Request Method.
In the Body field, input this JSON:
{ "notifyOption": "MailOnFailure" }
Press the Test Request/Response button to test the request and refresh the dataset at the same time.

Configuring REST API Task to refresh Power BI dataset using Power BI REST API and SSIS.

Configuring a REST API Task to refresh a Power BI dataset using Power BI REST API and SSIS.

Run the package

Hit F5 and execute the package:

Executing SSIS package and refreshing Power BI dataset.

The Results

Now it’s time to check the results and see if the dataset was refreshed successfully:

Again, go to Power BI datasets.
Then click on Datasets.
Click the dataset you refreshed.
Then click “Refresh history” to check the refresh history. We expect two manual refreshes to be there.

Results: Successful Power BI dataset refresh using Power BI REST API and SSIS.

Debugging HTTP requests

A common thing you have to do when working with HTTP requests is to debug those requests; e.g. to check what headers, body or URL was passed. To test how things look behind the scenes we strongly suggest to use Fiddler - a popular web debugging tool.

Inside it, you can double-click the URL entry (Right side) to see Request and Response Panels. The top panel is Request (URL, Headers, Body) and Bottom Panel is Response. For https:// (secure URL) make sure you enable HTTPS option in Fiddler (Tools > Options > HTTPS > Check Decrypt https request):

Debugging Web API call using Fiddler in SSIS

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

Check our article on how to configure packages with sensitive data on your production or development server.

Conclusion

You learned how to refresh a Power BI dataset with REST API using SSIS. To accomplish the goal, firstly, we created the Azure Active Directory application and configured OAuth Connection Manager based on it. Then we retrieved the Power BI dataset ID and used it in the ZappySys SSIS PowerPack connector REST API Task. Finally, using the same REST API Task we made the HTTP request to Power BI REST API “refreshes” method and successfully refreshed the dataset. If you need to make a call to another Power BI REST API method, then just configure the REST API Task according to the documentation (for getting data use JSON Source or Web API Destination instead). Finally, if you make many requests to the Power BI REST API service, make sure you check its limitations.