Share via

SSIS data flow fails with proxy from SQL Agent

bukkybu 346 Reputation points
2022-03-02T22:17:08.297+00:00

I have a very simple SSIS package with a data flow that reads from SQL and writes to Excel (on a different server in the same domain) then sends the .xlsx as an attachment in an email. It runs successfully when run from Visual Studio. However, it fails when run from SQL Agent with a proxy. The proxy's credential is also the same domain. If I make the proxy's credential a local admin on the SQL Server, the job runs successfully. It also runs successfully if I log on to the SQL server with the credential account. Granting the account Remote Desktop Users is not enough. The account has to actually be logged on to the server.

The execution error I get is the generic 'Unexpected Termination'.

Here is what I have tried that did not work:

  • Grant the credential full control on the C:\Users\Default\AppData folder
  • Grant the credential 'Log on as a batch job'
  • Add the credential to Distributed COM Users
  • Log on to the server as the credential to ensure the credential has a C:\Users folder with sufficient space
  • I made sure the SQL Agent job step is running 32 bit

I have worked with SSIS for years with the same types of data flows with no issue. This is running in SQL Server 2019 on Windows Server 2019

Any assistance is greatly appreciated.

UPDATE: These are the conditions under which the job seems to consistently run successfully:

1) When I log on to the SQL server with the credential account. Granting the account Remote Desktop Users is not enough. The account has to actually be logged on to the server.

2) When the credential is a local admin on the SQL Server.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,018 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,479 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 34,346 Reputation points
    2022-03-03T06:37:29.997+00:00

    Hi @bukkybu ,

    Unexpected Termination

    The error message is general for us to narrow down the issue.

    Could you please use process monitor or SQL Server Profiler to catch more details to analyze the issue?

    I suppose that the issue may be related with the account to access the SMTP server.

    Check this two links about the same issue, hope it could be useful.

    ssis-send-mail-task-doesnt-run-through-sql-server-agent-job

    ssis-package-when-executed-through-job-executes-successfully-but-it-does-not-sen

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.