SSIS package reading xlsx unexpected termination using proxy user

Question

I have a SSIS package that needs to read an excel (xlsx) file stored on a share on a server. The package runs flawless in Visual Studio but fails when run with the Agent on SSIS service.

I created a proxy user linked it to a credential that uses a login of a user that has access to the file and folder and under which I installed the Office Runtime (for access to the Excel file, version 16). I found out that the scheduled package runs successful when the user linked to the credential is logged on to the server on which SSIS runs. But when I log off the user the schedule fails.

Why does this user need to be logged on (have an active session) on the server for the package to run? What do I overlook here, because I don't want to consume resources with an active session on the server and prevent issues when the server reboots and the session is no restarted.

Thank you for your feedback

Answer 1

Hi @Guy De Backer,

Here you have a great article about set the SSIS Proxy users:

Running a SSIS Package from SQL Server Agent Using a Proxy Account

You may have a check to see if you miss any steps.

Make sure that you have set the job to run with SSIS proxy account.

If this do not work, please refer to this thread which has the same issue.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Thank you Zoe

But that is not my problem, I have similar packages using the same proxy user consuming csv files stored on the same location that always work flawless.

I only have problems with the package consuming an excel file and even then only when the user who's credentials I use for the proxy user is logged on to the server on which SSIS runs. The problem is that I don't know where to find more information on what exactly goes wrong when it fails (the report only shows unexpectd termination, and it terminates in the validation phase). Maybe there are logs that contain more info, but I don't know where to look for them and analyse further.

But I'll try to check if the user has been granted the "logon as a batch job" rights. It feels as if that might be the problem. I'll return once that is sorted out and tested.

Answer 3

I am assuming that the SSIS package is failing because the user that is linked to the credential is not logged on to the server due to permissions issues. When a user X is logged on to the server, his credentials are used to access the shared Excel file and this is how the SSIS package can access the file without any issues. If not the SSIS package running under the context of the SQL Agent will end up failing because of insufficient permissions to access the file.

You can try granting the necessary permissions to the SQL Server Agent service account directly on the share where the Excel file is stored.