Microsoft.Office.Interop.Excel Throws Exception

  • Squatt Gmail


    Points: 422

    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)


    Call ReleaseCom(lobjWorkBook)

    'Destroying The Excel Object


    Call ReleaseCom(lobjExcel)


    Dts.TaskResult = ScriptResults.Success

    End Sub

    Sub ReleaseCom(ByVal o As Object)


    If Not o Is Nothing Then


    End If

    Catch ex As Exception

    o = Nothing


    If Not o Is Nothing Then o = Nothing

    End Try

    End Sub


    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!



  • frederico_fonseca


    Points: 14686

    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


    Points: 422

    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?



  • Lowell

    SSC Guru

    Points: 323450

    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 7 months, 1 week ago by  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: 244589

    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?


    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 7 months, 1 week ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

  • frederico_fonseca


    Points: 14686

    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.

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

    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.

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

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