Refresh Excel file using SSIS

copied from Jessica Moss' blog, in case that blog is ever offline.

http://jessicammoss.blogspot.com/2008/10/manipulating-excel-spreadsheets-in-ssis.html

Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name):

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Success

Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

wb = excel.Workbooks.Open("C:\\TestExcelSS.xlsx")
wb.RefreshAll()
wb.Save()
wb.Close()

excel.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(excel)

End Sub

End Class

You'll see error squiggles, but don't worry about them because they will disappear in just a minute. Save and close your package. In your Solution Explorer, right click on the package and select ‘View Code’.

In the resulting XML, change the Build Settings ReferencePath property to:
"C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\"

Also change the Build References to include:
Name = "Microsoft.Office.Interop.Excel"
AssemblyName = "Microsoft.Office.Interop.Excel"
/>

Save the XML, and reopen the package. Open the script task and select ‘Save’. This will compile the code, and now you can run your package.

When working with COM references, you can use the script task GUI to add the reference by adding the desired component to the .NET framework folder. I could not find Microsoft.Office.Interop.Excel.dll on my machine to move to the framework folder, which is why we added the reference through the XML.

As Douglas Laudenschlager notes, writing server-side code to access client-side Office is unsupported. Please take these possible problems under advisement and code as necessary. You have been warned. :)

Update (11/12/08): Added last two lines to code to stop Excel process.

Versions: Microsoft Office 2007, SQL Server 2005 SP2

Posted by Jessica M. Moss at 1:11 AM

Download excel file from SSRS using RS

rs -i RSDownloadFile.rss -s http://localhost/reportserver

-------->8 RSDownloadFile.rss 8< --------

Public Sub Main()
Dim fileName as String = "Refreshable_Pivot.xlsx"
Dim strResourcePath as String = "/TestFolder/Refreshable_Pivot.xlsx"

'Dim rs As New ReportingService
Dim myByteArray() As Byte
myByteArray = rs.GetResourceContents(strResourcePath, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(myByteArray, 0, myByteArray.Length)
stream.Close()
End Sub