Agent job hangs unless logged in remotely to SQL Server

dsk 1 Reputation point
2021-09-13T10:05:18.767+00:00

I call .xlsm, which is automatically executed by the SSIS Process Execution task.
Then I register this to the SQL Server Agent job.

It works fine when I am logged in.

However, if it is run when I am logged off, such as during a scheduled run, it hangs up.
I can see the Excel process in Task Manager, but it is not actually running (CPU usage is 0% all the time).

What are the possible causes?
Note that I am not using a user DSN, so that is not the cause of the problem.

Thanks.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,697 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-09-13T21:45:43.897+00:00

    Excel needs a desktop. Services do not have desktops. Thus, invoking Excel from a service, for instance an Agent job, is not the best of ideas.

    0 comments
  2. YufeiShao-msft 7,056 Reputation points
    2021-09-14T06:21:17.523+00:00

    Hi @dsk ，

    A Desktop folder and give write permissions on the Desktop folder seems to be necessary to open file by Excel,
    https://social.msdn.microsoft.com/forums/sqlserver/en-US/4321f28c-0a89-4694-a3a4-c0459d002c61/ssis-package-which-reads-excel-files-fails-on-sql-server-agent

    Or the SQL Server Agent service account may not have sufficient permissions, check what user will be running the SQL Server agent job when you are not logged in.

    And the job created may be just processing the Excel only, may be you should change the SSIS package to process the whole database.

    0 comments
  3. dsk 1 Reputation point
    2021-09-22T02:40:10.803+00:00

    Hi All,
    Thanks for the answers.

    I took a hint from your answers and the problem was solved.

    I stopped calling the .xlsm from the job.
    I created a VBS that calls .xlsm and registered it to the job and it worked.

    0 comments