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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
public void Main() { const string DataSourceMarker = "data source="; // refresh each Excel connection manager foreach (var conn in Dts.Connections) { if (conn.CreationName == "EXCEL") { string filepath = null; var parts = conn.ConnectionString.Split(';'); foreach (var part in parts) { if (part.StartsWith(DataSourceMarker, StringComparison.OrdinalIgnoreCase)) { filepath = part.Substring(DataSourceMarker.Length); break; } } if (filepath != null) { ExcelRefresh(filepath); } } } Dts.TaskResult = (int)ScriptResults.Success; } private void ExcelRefresh(string filename) { object NullValue = System.Reflection.Missing.Value; var excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.DisplayAlerts = false; var Workbook = excelApp.Workbooks.Open( filename, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue, NullValue); Workbook.RefreshAll(); System.Threading.Thread.Sleep(20000); Workbook.Save(); Workbook.Close(false, filename, null); excelApp.Quit(); Workbook = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } |
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
