Microsoft.Office.Interop.Excel Throws Exception

Question

Microsoft.Office.Interop.Excel Throws Exception

Squatt Gmail

SSC-Addicted

Points: 422
More actions
December 3, 2019 at 7:15 pm

#3703969

I am testing an SSIS package to see if we have all required software installed on the server to run SSIS jobs.
Server config:
Windows Server 2016, SQL Server 2016, Visual Studio 2017 (on server)
Package has 1 Script Task, using VB.Net 2017, added Reference to Microsoft Excel 15.0 Object Library for Interop assembly.
Script Code:
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
#End Region
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Public Sub Main()
Dim lobjExcel As Object 'Instance Of The Excel Application Object
Dim lobjWorkBook As Object 'Instance For WorkBook Collection
Dim lstrExcelFile As String = CType(Dts.Variables("User::LabExcelFileFullPath").Value, String)
Dim lstrTextFile As String = CType(Dts.Variables("User::LabTextFileFullPath").Value, String)
lobjExcel = CreateObject("Excel.Application")
lobjWorkBook = lobjExcel.Workbooks.Open(lstrExcelFile)
lobjExcel.DisplayAlerts = False
'Saving In The Tab Delimited Format
lobjWorkBook.SaveAs(lstrTextFile, -4158)
lobjWorkBook.Close()
Call ReleaseCom(lobjWorkBook)
'Destroying The Excel Object
lobjExcel.Quit()
Call ReleaseCom(lobjExcel)
GC.Collect()
Dts.TaskResult = ScriptResults.Success
End Sub
Sub ReleaseCom(ByVal o As Object)
Try
If Not o Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
End If
Catch ex As Exception
o = Nothing
Finally
If Not o Is Nothing Then o = Nothing
End Try
End Sub
Exception:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
It's been a while since I have coded in VB.Net so I am probably missing something simple. I inherited this from another developer. Any advice would be great!
Scott

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

are you aware that interop requires Excel to be installed on the server?

Not only that is not supported by Microsoft but it also has licensing implications and costs that your company may not be expecting.

and its not just software that needs to be installed - sometimes particular folders have to be created for "UI based software" to work as a service

Squatt Gmail SSC-Addicted Points: 422 More actions · Answer 2

Frederico, thanks for the quick response. Unfortunately, I work for a company that is highly regulated by the state as we provide Medicare & Medicaid. Therefore installing Office products on a server is not allowed. It took the sign-off of the CIO just to get Visual Studio installed. It is necessary (dictated by vendors) to be able to manipulate Excel files like renaming Sheets and in the example above, saving the Workbook in a text format.

Any ideas?

Scott

Lowell SSC Guru Points: 323518 More actions · Answer 3

so looking at the code, it is using Excel Interop to save to a tab delimited file;

can you eliminate the middleman and just read the excel document with SSIS, and use the dataflow to export out to a flat file?

why the extra work? does the sheet change with each iteration or something, so the columns change?

OpenXML 2.5 is a .NET library you can use to do similar work. I use it to make highly customized excel documents.

This reply was modified 5 years ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246987 More actions · Answer 4

Squatt Gmail wrote:

It is necessary (dictated by vendors) to be able to manipulate Excel files like renaming Sheets and in the example above, saving the Workbook in a text format.
Any ideas?
Scott

You can't do this stuff in SSIS unless you install Excel in the same place as where SSIS is running.

Edit: just saw Lowell's reply. I have not used OpenXML, but it sounds promising.

This reply was modified 5 years ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

frederico_fonseca SSCoach Points: 16167 More actions · Answer 5

if that regulated then installing Visual Studio on the server is also not allowed - again licensing issues.

using OpenXML (2.9 better than 2.5 but harder to install) is one way.

http://www.microsoft.com/en-ie/download/details.aspx?id=30425

https://www.nuget.org/packages/DocumentFormat.OpenXml/

For other things just using Microsoft Access Runtime (which can be installed on servers and is supported) is another option to read/write Excel files but it won't allow you to rename tabs - for that OpenXML is the way to go.

In either case you will not be able to run macros - just in case whoever is doing the development also though of being smart and using macros.

I am aware of one company(Willis Towers Watson) which has software where they wish to use Excel on server. They have been advised before that they needed to move to OpenXML precisely because of this issue - Other companies with similar software also need to do the same.

palandri Old Hand Points: 387 More actions · Answer 6

I have used the MS Access database engine to process Excel files in SSIS. It's a library, so no user interface, if that makes a difference, regulation-wise.

My wife has a saying, "Like's to short to ____________". Pretty much anything you don't want to do can fill in the blank. That said, life really is too short to work with Excel files in SSIS. I only use the library to convert them to .csv in a script task before the data flow. I have not used OpenXML, but if/when I have to do a new package with Excel input, I will certainly look into it.

The Access database engine is available here.

https://www.microsoft.com/en-us/download/details.aspx?id=54920