When running script in SSIS to modify a excel file get error :The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))

Question

When I try running a SSIS script to update an excel file it always errors on any modifications or save reading the file is ok, this is done in Visual studio 2005 and run on a server.

Any ideas.

The version of excel Interop is : Microsoft.Office.Interop.Excel - 11.0.0.0

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.office.interop.excel.dll

Script Code:

    Dim oExcel As Excel.Application
    Dim oWorkBook As Excel.Workbook
    Dim oWorkSheets As Excel.Sheets
    Dim oWSheet As Excel.Worksheet
    Dim oRng As Excel.Range
    Dim oCell As Excel.Range

    Dim Temp As String
    Dim startCol As String
    Dim startRow As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim i As Long

    Dim myfile As String
    Dim myPath As String


    'SET oExcel As Excel.Application
    oExcel = CreateObject("Excel.Application")

    'DISABLE EXCEL WARNINGS
    oExcel.Visible = False
    oExcel.DisplayAlerts = False
    oExcel.AskToUpdateLinks = False
    oExcel.AlertBeforeOverwriting = False

    myPath = "\\Network Path\Testing\"
    myfile = "EIV Temp File - Small.xls"

    oWorkBook = oExcel.Workbooks.Open(myPath & myfile)

    With oWorkbook
        oWSheet = CType(oWorkBook.Sheets(1), Excel.Worksheet)

        startCol = "A"
        startRow = 1
        lastRow = oWSheet.Range(startCol & oWSheet.Rows.Count).End(XlDirection.xlUp).Row
        lastCol = oWSheet.Cells(2, oWSheet.Columns.Count).End(XlDirection.xlToLeft).Column + 1

        '  oRng = oWSheet.Range("I2" & ":" & "I" & lastRow)

        ' For Each oCell In oRng
        ' Temp = "'" + oCell.Value
        ' oCell.Value = Temp
        ' Next oCell

        Try
            With oWSheet
                For i = 2 To lastRow
                    Temp = "'" + .Cells(i, 9).value
                    .Cells(i, lastCol).value = Temp ' Errors at this line
                Next i
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        .Save() ' Errors at this line
        .Close(SaveChanges:=False)
    End With

Exception

Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)

Answer 1

I tried your code, and the following line was throwing an exception:

Temp = "'" + .Cells(i, 9).value

because you are using + operator to concatenate strings, while if the value of the cell is not string it will throw an exception. try the following syntax.

Temp = "'" & .Cells(i, 9).value

Also you can remove .Save line and use .Close(SaveChanges:=True)

Side note:

After the script is executed the excel will remain opened in the background, you need to add the following lines:

Marshal.ReleaseComObject(owsheet)
Marshal.ReleaseComObject(oWorkbook)
Marshal.ReleaseComObject(oExcel)