SSIS Script Task Not Running Excel Macro With AddI

I have an SSIS Script Task that calls an Excel workbook macro. This Excel macro uses an Excel AddIn (xlam) for logging. When the code runs it blows up unless during debugging, I put a breakpoint in the workbook, and then step through the Excel part. If I step through it once, my process then can run via SQL Server Agent, and in automation again. Any idea why I need to put in a breakpoint to fix the process?

Here is my sample SSIS Script Task Code:

        Dim xlApp As Excel.Application = Nothing
    '
    Try
        xlApp = New Excel.Application
        xlApp.Visible = True
        xlApp.Application.DisplayAlerts = False
        xlApp.Workbooks.Open(strExcelDriverFile)
        xlApp.Run("CreateReport")
        Dts.TaskResult = ScriptResults.Success
        xlApp.Workbooks.Close()
        xlApp.Quit()
    Catch ex As Exception
        xlApp.Quit()
        Dts.TaskResult = ScriptResults.Failure
    End Try

Here is my sample Excel Code:

Sub CreateReport
'Log using the addin
LogHWMsg Replace(Replace("Starting Macro", "'", ""), """", ""), GExecGuid, 0, 1, ThisWorkbook.Path, ThisWorkbook.Name, _
                ActiveWorkbook.Path + "\" + ActiveWorkbook.Name, Environ("UserName")
'Do some stuff...

End Sub

Excel Automation does automatically not load add-ins. You need to specifically load them via code.

The following example is from Loading Excel Add-Ins at Runtime (Written by: Jeremy Espenshade)

The following options are available if you want to do this.

1.) Application.RegisterXLL

a. This is a method which can be called from VBA which loads an XLL at a specific location and registers the functions and commands contained in the XLL.

2.) AddIns.Add

a. This is a method which can be called from VBA which loads any type of add-in (XLL, XLA or XLAM). After loading the add-in, perform step 3 to open it.

3.) AddIn.Installed = true

a. Once you have a reference to a loaded add-in, set AddIn.Installed = true to cause the add-in to be opened.

b. Note that add-ins that are known when Excel is started with the /automation switch will already be marked as "Installed", but they are not opened. In this case, Set Installed = false before setting Installed = true

Private Sub Workbook_Open()
    Dim success As Boolean
    Dim myAddIn As AddIn

    ' Load XLL
    success = Application.RegisterXLL("c:\myaddins\myxll.xll")

    ' Load and install new XLAM
    Set myAddIn = Application.AddIns.Add("c:\myaddins\myxlam.xlam")
    myAddIn.Installed = True

    ' Load known XLAM
    For Each myAddIn In AddIns
        If myAddIn.Name = "myknownaddin.xlam" Then
            myAddIn.Installed = False
            myAddIn.Installed = True
        End If
    Next
End Sub

Edit: The OP has asked that I include the technique that worked for him; i.e. activating the add-ins that are available to the user account under which Excel is executing.

The AddIns object is a property of the Excel.Application object. When you are using Excel automation, the Excel application will not automatically load the AddIns that would be loaded in an interactive session. Therefore, you need to use the proper technique demonstrated above to load the add-in based on its type.

Example Activating Known Addins:

xlApp = New Excel.Application
For Each addin As Excel.AddIn In xlApp.AddIns
    If Not String.IsNullOrWhiteSpace(addin.Path) Then
        addin.Installed = True
    End If
Next

Please take not that the code verifies that the Path property is not empty. This can be cause by an add-in that was uninstalled.

Excel is also a pain about maintaining links to previously loaded add-ins. In fact the only way to remove on from the list is to make the the file unavailable from the originally loaded path and to specifically ask Excel to remove it via a dialog window once Excel can not find the add-in. Therefore, a Known add-in may not be accessible. To make this worse, setting the Installed property on an inaccessible add-in does not throw an error.

However an error will be thrown if you try to access any member of the unloadable add-in.