Microsoft.Office.Interop.Excel Throws Exception

  • Squatt Gmail

    Old Hand

    Points: 301

    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

     

  • frederico_fonseca

    SSChampion

    Points: 14216

    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

    Old Hand

    Points: 301

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

    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 days, 19 hours 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: 243844

    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 days, 18 hours 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.

  • frederico_fonseca

    SSChampion

    Points: 14216

    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

    SSC Veteran

    Points: 248

    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

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

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