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.


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).


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

10 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

  6. John says:

    This is exactly what I'm looking for. However, I'm receiving the following error:

    "Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead.C:Usersv-labernAppDataLocalTemp3VstaSSIS_ST110Vstaa0g_GqLLAUKVI5Qh9iJUgAVsta3Qj6FXpFoUeOs0D3X2z87QScriptMain.cs12963ST_1928fa4ea8ff431988bf39f246c51051"

    Also, is there some step by step guide on how to disable "Embed Interop Type" option within Project Explorer? How to open Project Explorer?

    Your help is much appreciated!

  7. Mike Honey says:

    Thanks for this technique Matt – very elegant.

    On some machines the Office install doesn't work for some reason and Microsoft.Office.Interop.Excel is not listed. I used this workaround:

    @John – while editing the Script, find the Solution Explorer pane (or open it from the View menu), then expand the References node and choose Microsoft.Office.Interop.Excel. Then open the Properties pane and look for "Embed Interop Type".

  8. Tommy says:

    Hi Mat,

    Appreciate you could elaborate your steps again using visual studio 2013. I am having problem to find how to ADD REFERENCE.

Leave a Reply

You must be logged in to post a comment.