How To - Automate Power Shell Data Source

Sometimes Power BI data source requires a csv to be populated by a Power Shell Script. Below are the steps to creating a source controlled Power Shell Script and automating the refresh with SQL Agent.

Instructions

  1. Clone Git https://github.com/ucsb/ucsb-sa-powerbi

  2. Open Visual Studio code and go to folder PowerShell/PBIDataSource

  3. Create power shell script and push

  4. Add power shell script to \sa8\data\PowerBI\PBIDataSource

  5. The csv should be located in subject specific folder in \sa8\data\PowerBI\<subfolder>

  6. Create SQL Agent Job with name in the following format: PBI - <descriptive name>

    1. create a step with type Power Shell . The step will have a command line:

PowerShell.exe -File \\sa8\data\PowerBI\PBIDataSource\<scriptname>.ps1

 

Related articles