With SSIS 2014 and earlier there is currently not native way to refresh an Excel workbook which include Power Query queries. Now that functionality is rumored to be included with SQL Server 2016 but if you’re currently running SQL Server 2014 or 2012 you are out of luck. But that’s why Pragmatic Works put together the Excel Power Refresh component for SSIS.

Configure the Excel Power Refresh Task in SSIS

Configuring the Excel Power Refresh Task is pretty straightforward. There’s not a lot of complexity to this component, which is a good thing.

First create a Connection Manager to your Excel 2013 file that includes your Power Query queries. In my case I have an Excel workbook that has some Power Query queries that query NFL.com for some data that I used for a blog post on the new Excel 2016 chart types.

Once you create your connection to the Excel file, use the Data Connections and Pivot Table Sheets to select the queries and pivot table sheets that you wish to refresh.

And now I can schedule the refresh of any Power Query data connections or Pivot Tables with SSIS.

Very cool!

Resources

Download the free trial of Task Factory here.

Feedback?

If you have any questions or would like some more information on Task Factory, feel free to send me an email or leave a comment below. Thanks!