Interop issues on SQL Server 2008 R2

First time I ask a question on this website but for the last two years I have consulted the website at least once a day as it is very helpful! However there is one issue I cannot resolve that is about Excel Interop I believe.

I am using SQL Server 2008 R2 and Visual Studio 2008 3.5 SP1. I am also working for a large company therefore I do not have Admin rights but I hope you can help me to understand the issue.

I am using Script Tasks in SSIS in order to call an excel file and run a macro. The code in this Script Task

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop
Imports System.Net.Mail
Imports System.Net.Mail.SmtpClient
Imports System.Net


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum


' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' 
' To open Help, press F1.

Public Sub Main()
    '
    ' Add your code here
    '
    Dim oExcel As Excel.ApplicationClass = Nothing
    Dim oBook As Excel.WorkbookClass = Nothing
    Dim oBooks As Excel.Workbooks = Nothing

    Try

        'Start Excel and open the workbook.
        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = False
        oBooks = oExcel.Workbooks
        oBook = oBooks.Open(Dts.Variables("XlFilePath").Value.ToString()) ' Change your variable name here.


        'Run the macros.
        oExcel.Run("CreateFiles") ' Change the name of your Macro here.

        'Clean-up: Close the workbook and quit Excel.
        oBook.Save()
        oBook.Close(SaveChanges:=False)

        'GC for the workbook
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oBooks)

        'Release ojbect
        oBooks = Nothing
        oBook = Nothing

        'Close Excel
        oExcel.Quit()
        'GC for the app object
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oExcel)

        'Release object
        oExcel = Nothing

        'Final GC
        GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced)
        GC.WaitForPendingFinalizers()
        'Collect anything that's just been finalized
        GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced)

        Dts.TaskResult = ScriptResults.Success

    Catch ex As Exception
        oBook.Close(SaveChanges:=False)

        If oBook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
        If oBooks IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
        If oExcel IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

        Dim htmlMessageFrom As String = "email"
        Dim htmlMessageTo As String = "email"
        Dim htmlMessageSubject As String = "Failure: warning on Excel report refresh: " & Dts.Variables("PackageName").Value.ToString()
        Dim htmlMessageBody As String = ex.ToString & Dts.Variables("XlFilePath").Value.ToString()
        Dim smtpServer As String = "smtpmail"

        SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody, True, smtpServer)



    Finally
        If oBook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
        If oBooks IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
        If oExcel IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)


        oBook = Nothing
        oBooks = Nothing
        oExcel = Nothing

    End Try
End Sub

And when I run the code it successfully access the Excel file and runs the macro.... however sometimes (mainly when this is scheduled as a job) I receive error messages, a few examples:

System.Runtime.InteropServices.COMException (0x80010100): System call failed. (Exception from HRESULT: 0x80010100 (RPC_E_SYS_CALL_FAILED)) at Microsoft.Office.Interop.Excel.WorkbookClass.Save() at ST_dabd3c9bca8e4418a6982a41605d0451.vbproj.ScriptMain.Main()\filepath\ExcelFileName.xlsb

or

Error in the task: Create Reports with the ID: {77e6cf1d-12b9-45b4-bdc8-b244512eee78} has failed at: 9/24/2014 12:10:01 AM. Description:System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object. at ST_dabd3c9bca8e4418a6982a41605d0451.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) 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()

but I don't understand anything from these messages and the most strange thing is that is occurring randomly.... it can work like it won't!

This just doesn't make any sense to me!

Thanks for your help on this matter.

One other error messages I receive randomly as well for the same task:

Error in the task: Run new BookingType code with the ID: {e1dabfc5-8b6b-4064-a980-4f5be465316b} has failed at: 9/25/2014 2:00:02 AM. Description:System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: Cannot create ActiveX component.

at Microsoft.VisualBasic.Interaction.CreateObject(String ProgId, String ServerName) at ST_dabd3c9bca8e4418a6982a41605d0451.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) 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 System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Object reference not set to an instance of an object.

This means the script tried to instantiate an object, and failed, and then tried to do something that depended on that object being there.

It makes sense that this error can occur randomly because it depends on conditions on your server. Trying to access a file that you expect to be there, but it's not. Or it doesn't have the sheet that you expect.

One way to code for this is to put conditional checks to verify your object exists immediately after instantiating it, and before trying to access it. Something like:

    'Start Excel and open the workbook.
    oExcel = CreateObject("Excel.Application")
    if oExcel isnot nothing then
      oExcel.Visible = False
      oBooks = oExcel.Workbooks
      oBook = oBooks.Open(Dts.Variables("XlFilePath").Value.ToString()) ' Change your variable name here.
      if oBook isnot nothing then
        'Run the macros.
        oExcel.Run("CreateFiles") ' Change the name of your Macro here.
        ...

And then of course if the object doesn't exist, it's up to you to decide what you want to do then. Even if it's just returning a more specific error, like "File x doesn't exist!"