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