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

11 thoughts on “Refresh an Excel Workbook with a Script Task”

  1. 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. 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. 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!

  4. 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".

  5. Hi Mat,

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

  6. Hi Matt,
    The objective is to loop through a folder and refresh all the excel file sin the folder.
    I have exactly the same code you have for ExcelRefresh(string filename) in a script task in SSIS package. It worked very well when I had it in SqlServer 2008 but when it was upgraded to SqlServer 2012, using Visual Studio C# 2010 and didn’t changed any of the code below, it worked and refresh the worksheet but it does not close the file. Hence the server has all these files still open and consuming the memory and eventually it ran out of memory and failed.

    I would really appreciate if you can help. Thanks.

    public void Main()
    string directoryPath = Dts.Variables[“User::FileName”].Value.ToString();
    string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, “*.xlsx”);
    foreach (string currFile in oldFiles)
    Dts.TaskResult = (int)ScriptResults.Success;

    private void ExcelRefresh(string Filename)
    object NullValue = System.Reflection.Missing.Value;
    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    excelApp.DisplayAlerts = false;
    Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
    Filename, NullValue, NullValue, NullValue, NullValue,
    NullValue, NullValue, NullValue, NullValue, NullValue,
    NullValue, NullValue, NullValue, NullValue, NullValue);


    Workbook.Close(false, Filename, null);
    Workbook = null;


Leave a Reply

Your email address will not be published. Required fields are marked *