SSIS Excel RefreshAll() task SQL job fails when no one logged in

Question

A few months ago, I made a SSIS package that is used to refresh a couple excel worksheets (+few other things)

Now, my college that is still on the project reports this weird behavior. The SSIS package is scheduled as SQL Agent Job.

It runs hourly from 8am00. At 8am it always fails, his conclusion: at 8am is no one logged on the server. The second time, at 9am, the job runs OK. (RefreshAll worked) His conclusion: there is always someone logged on the the server via RDP at that moment.

(In fact I don't know about the other runs later on the day)

The task is a VB Script task that calls the Excel Interop dlls. I remember having difficulties to get it working until I installed Excel 2010 x86 on the server. -> Excel is fully and legitimately installed on the server.

My guess and determination at that moment was that it sometimes went wrong somewhere and Excel did not close properly. When I opened taskmgr I found +10 instances of Excel.exe running... This was during development.

My college did an interesting test: scheduled the job every minute and logged on and off a few times to the server. Every time no one was logged on to the server (RDP) the job failed. When logged on, the job ran OK !

Below the code that is used in the 'RefreshAll' script task.

I also used threading.sleep because otherwise I got timeout errors. Found no other way.

Thanks in advance!! L

Public Sub Main()
        Dts.TaskResult = ScriptResults.Success

        Dim oApp As New Microsoft.Office.Interop.Excel.Application
        oApp.Visible = False
        'oApp.UserControl = True
        Dim oldCI As System.Globalization.CultureInfo = _
            System.Threading.Thread.CurrentThread.CurrentCulture
        System.Threading.Thread.CurrentThread.CurrentCulture = _
            New System.Globalization.CultureInfo("en-US")

        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        wb = oApp.Workbooks.Open(Dts.Variables("User::FileNameHandleFull1").Value.ToString)
        oApp.DisplayAlerts = False
        wb.RefreshAll()
        Threading.Thread.Sleep(10000)
        wb.Save()
        wb.Close()
        oApp.DisplayAlerts = True

        oApp.Quit()
        Runtime.InteropServices.Marshal.ReleaseComObject(oApp)

    End Sub

End Class

b0rg b0rg · Accepted Answer · 2012-03-02T14:30:25

Excel is a client application and normally it does require to have active user session on a machine it runs on. For a job like this, I would consider other approaches not involving working with excel process, eiter:

store the result in a sql server table, then use linked table from excel sheet to pull the data.
use export as .csv (comma separated values)
use 3rd party controls that write Excel format
they may fix it in next version of Excel?

SSIS Excel RefreshAll() task SQL job fails when no one logged in

1 Answers