Push Data to Power BI Service using SSIS component

With the growing use of Power BI for visualizations, analytics and analysis, we look into the ways of integrating data to Power BI. Though Microsoft provides  API to push data to Power BI Services, it has some restrictions.


This article provides an example of using free SSIS component PBIPushData to push data to Power BI.

PBIPushData is a SSIS Destination Component to load data to tables on Power BI Service. Power BI has a restriction where in data can only be pushed to the tables created by Power BI API. PBIHelper Desktop Application is used to create datasets and tables in Power BI Service via API. The component PBIPushData and PBIHelper application works with both free and Pro Power BI Service.


In this example, dataset Human Resources is created in Power BI Service using Power BI Helper desktop application. Data from source file Departments is pushed to Departments table in Power BI.

Overview:
In this example a free SSIS component Power BI Push Data is used to upload data to Power BI. The component uses Power BI APIs to communicate with Power BI service.

In general, to use Power BI APIs, we need to register the application with Power BI service. The application also needs to be authorized by Azure. This is one time process.  The article “App registration for Power BI API use via Azure Portal” explains required steps to register app in detail.Client ID obtained during registration will be used along with credentials to communicate with Power BI service.

Create datasets using PBIHelper Application                                                                  Download Demo Files

 Download and install PBIHelper application.

 

PBIHelper application opens up with screen to input Power BI credentials. The credentials will be saved on local machine and will be used to logon to Power BI Service. Along with credentials, Client ID obtained in Step 1 is required. Check “Use Power BI Preview (beta) API”, to define measures and relations. Click here for more info on using Power BI Preview (beta) API.

  • Logon : Provide Power BI credentials and save.
  • Select the account and click connect.
  • Click on button New Dataset.

In screen Create dataset & tables, enter dataset schema information.

Name: HR
Click on [+] in Group Tables. Define table Departments
  

Click on [+] in Group Columns. Define following columns.


Column Name                                       Data Type

========================================
DepartmentID                                           Int64
Name                                                        String
GroupName                                             String
ModifiedDate                                            DateTime

Click on button Create Dataset & Tables.

This creates dataset in Power BI service.

 

“Create dataset & tables…” screen facilitate defining schema for dataset. If Power BI API in beta/preview mode is in use, measures can be defined, as well table relations. Dataset schema definitions can be saved before creating dataset in Power BI service.

Install SSIS component PBIPushData & Load data to datasets in  Power BI Service

Download and install SSIS Destination Component PBIPushData  (   SQL 2012       SQL 2014       SQL 2016 )

Once installed PBIPushData destination component will be available in Common components section of Data Flow Task.

Demo package is a template/skeleton package. The source and destination components need to be configured for your local path and with your Power BI settings.

The Client ID obtained in Step 1 is required.

  •  Drag & Drop Data Flow Task in your SSIS package.
  • Open DFT.
  • Drag & drop Flat File source component and configure source from downloaded demo file Departments.csv.
  • Drag & drop component PBIPushData.
  • Open the component PBIPushData component.


            Enter Power BI Credentials on tab Connection Settings.
            Enter Client ID obtained in Step 1 as Client Key.

Column Mappings

  • Click on Column mappings tab.
  • Select HR as dataset.
  • Select Table Departments

 

Click on AutoMap to map source and target columns automatically.

Click on  Save & Close. Package is ready to push data.

 

Conclusion

In this post, we are looking at pushing data to Power BI services using custom SSIS components. This solution helps on-demand data push to Power BI service. This is very useful for users with the need of pushing data such as On-premise legacy IoT, users of free Power BI service to name a few.

Close Menu