SSIS package reading xlsx unexpected termination using proxy user

Guy De Backer 10 Reputation points
2023-03-07T09:32:07.2233333+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,700 questions
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 41,481 Reputation points
    2023-03-08T03:13:40.6466667+00:00

    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.

    0 comments No comments

  2. Amira Bedhiafi 32,911 Reputation points Volunteer Moderator
    2023-03-08T14:36:42.1166667+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.