SSIS 2008 and Excel Interop assemblies

Question

Hi All,

Have an SSIS 2008 package that runs just fine on my local dev machine with Office 2007 installed. It has a script task with interop.excel as a reference. (I'm reformatting some excel sheets with it)

So everything works like a champ until I install and run it on my test SQL 2008 (Server 2008 64bit) server. I install to SSIS, execute it via a SQL Server Job, it runs though most of the steps but then throws an exception when it gets to the script task that needs the excel interop assembly.

I've installed the 2007 PIA and have execution marked as 32bit as well. At this point I'm just kind of lost. Any help is appreciated.

Answer 1

This script task - Is it a .NET script task or a 32-bit script task?

I'm guessing from the interop.excel reference, that its a .NET script task calling out to an old 32-bit library? Can you confirm?

If there is a 32-bit component that you are running on your Win64 environment then you need to be careful about what you are using to register it. By default, regsvr32 is the 64-bit version, so you need to use the regsvr32.exe under c:\windows\systemWOW64 (or something similar). This will ensure the dll is registered in the 32-bit hive of the registry, and available to the WOW (windows-on-windows) emulation environment.

Answer 2

SpreadsheetGear for .NET is an Excel compatible spreadsheet component for 32 bit and 64 bit .NET, and has an API which is similar to Excel's COM API.

You can see some live ASP.NET samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

I installed Office 2007 on the server I was using. That fixed one problem. Then I discovered another problem that was alleviated by this SO Link