Refresh an Excel Workbook with a Script Task

Are you loading data from an Excel workbook with external queries using SSIS? Do you want to make sure those queries are refreshed before the load takes place? Have I got the post for you! I recently had to do this myself, and after some googling with Bing, I found an MSDN forum post that describes how to refresh all of the queries within a workbook using an SSIS Script Task. I’ve adapted the code from the post, and you can find it below.

For the code to compile, you’ll need to add a reference to Microsoft.Office.Interop.Excel within your script task’s project.

image

Next, select the reference under the project explorer, and open the properties window. You’ll need to disable the Embed Interop Types option. (This assumes you’re using SQL 2012, which supports .NET 4 … if you are using 2008 with .NET 2/3.5, you won’t have to do this).

image

Include the following code in your script task. It iterates over all of the Excel connection managers within your package, loads the workbook, and calls RefreshAll() to refresh each of the queries in the workbook. The task then sleeps for 20 seconds (to give time for the query refresh to complete) before exiting.

I kept the code simple for this example, but there are a few different optimizations you can make here:

  • Add error handling
  • Pass in the excel file name through a variable instead of iterating over each connection manager
  • Refresh a specific query within the workbook, rather than calling RefreshAll
  • Find your query within the workbook and set the BackgroundQuery property to False to avoid the asynchronous refresh call, and having to sleep

6 Responses to “Refresh an Excel Workbook with a Script Task”

  1. Koen Verbeeck says:

    Nice post, very useful. I assume you can use this to refresh Data Explorer queries as well? Because that would be great! SSIS and Data Explorer working together, because Data Explorer can easily handle some sources SSIS is having trouble with.

  2. Koen Verbeeck says:

    OK, I was thinking to write a blog post about refreshing data explorer queries so I could automate data loads of an Odata feed (referencing your code of course), but I'll leave it to the master :)

  3. [...] the workbook. Thankfully, we can workaround that using a Script Task – see my previous post about dynamically refreshing a workbook. Go ahead and ready that post real quick – I’ll [...]

  4. [...] modified version of the code found in Matt Masson’s blog post would be my preferred method to refresh Power Query excel sheets; if for some reason I [...]

  5. Jeff says:

    for which excel version is that considered? I have 2010, would it work?? I also ooked at this website accurate, not bad http://www.excel-aid.com/excel-refreshpivot-table

Leave a Reply

You must be logged in to post a comment.