Tuesday, October 21, 2008

Manipulating Excel Spreadsheets in SSIS

Tom, an attendee at last weekend’s SQLSaturday Olympia, asked me how to refresh a spreadsheet from within SQL Server Integration Services. My first thought was to turn on the connection’s “Refresh data when opening the file” option in the spreadsheet itself and avoid the situation entirely; however, this may not always be a viable solution. Here are the steps to perform the refresh from within an SSIS package.

First, ensure that Microsoft.Office.Interop.Excel is registered in the GAC. If not, install the 2007 Microsoft Office system Primary Interop Assemblies. This will need to be done on any machine where you plan on running this package.

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:
<Reference
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

Sunday, October 12, 2008

IIS 7.0 Role Service SSRS Requirements

Using the default IIS 7.0 installation on Windows Server 2008 does not include all of the role services necessary to install SQL Server Reporting Services 2005. I found this command in an online forum when looking for a list of all the needed role services. (By the way, I apologize, but I can no longer find the post to link back to. If it was you who wrote it, please post a comment with a link back to that post!)

Here is the command. I hope it helps you as much as it helped me!

ServerManagerCmd.exe -i Web-Server Web-Asp-Net Web-Http-Redirect Web-Windows-Auth Web-Metabase Web-WMI

Version: SQL Server 2005

Wednesday, October 8, 2008

Microsoft MVP

On October 1, 2008, I received notification that I was awarded as a Microsoft Most Valuable Professional. According to Microsoft, MVPs are "exceptional technical community leaders from around the world who are awarded for voluntarily sharing their high quality, real world expertise in offline and online technical communities" and quite a few other comments that make me blush. It's always nice to be rewarded for doing what you love to do.

MVPs are awarded for their past contributions, but the benefits are applied to the upcoming year. These benefits include access to technical resources and the opportunity to learn from and offer thoughts to Microsoft. I believe the information I provide will be best if it comes from everyone, so I ask you to let me know if you're having any issues or concerns with SQL Server. On a final note, if you have a user group that would enjoy learning about SQL Server BI, or if you have a technical question, or if you just feel like geeking out about SQL Server, please contact me at jmoss at solidq dot com.

Thank you.

Friday, October 3, 2008

Richmond and Olympia

That would be Richmond in Virginia and Olympia in Washington. :)

I'll be speaking at the Richmond Code Camp 2008.2 on October 4 about custom code in Reporting Services. Richmond is my local user community, and I can't say enough good things about the crew that organizes and speaks in that area. I hope to see some familiar faces during the day.

The following Saturday (October 11), I'll be presenting at Olympia SQLSaturday about Reporting Services 2008. I enjoy SQLSaturday events because they are all about SQL Server! This one includes talks on Business Intelligence, TSQL, and internals.

Both events have a great speaker lineup and should be a lot of fun. Hope to see you there!