Permissions Issue Through a Job

scott Hanebutt 21 Reputation points
2021-03-19T01:43:04.623+00:00

SQL Server 2017

I have created an SSIS package that creates a text file on the "Y" drive which is mapped to another server. When I run the package through the design interface it correctly creates the file. However when I have a SQL job run the package I get the following error.

Executed as user: DOMAIN\DomainAdmin. Microsoft (R) SQL Server Execute Package Utility Version 14.0.1000.169 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 9:28:43 PM Error: 2021-03-18 21:28:44.93 Code: 0xC020200E Source: Data Flow Task 1 Flat File Destination [2] Description: Cannot open the datafile "Y:\FILE5945031821.txt". End Error Error: 2021-03-18 21:28:44.93 Code: 0xC004701A Source: Data Flow Task 1 SSIS.Pipeline Description: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:28:43 PM Finished: 9:28:44 PM Elapsed: 1.063 seconds. The package execution failed. The step failed.

When I run it successfully I am logged on as DOMAIN\DomainAdmin. That account is a domain admin. This appears to be a permission issue however the package is being ran under the same credentials both ways. Any help would be appreciated.

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,682 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
Accepted answer
  1. Olaf Helper 40,741 Reputation points
    2021-03-19T07:15:06.47+00:00

    on the "Y" drive which is mapped to another server

    Mapped = so it's a NAS share mapped as drive in your profile; SQL Server service account don't have a profile and even, it's not yours and so drive Y don't exists for SQL Server engine.
    Use an UNC path instead and ensure that SQL Server service account do have access permissions on the NAS share.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,891 Reputation points
    2021-03-19T03:09:31.493+00:00

    Hi @scott Hanebutt ,

    1.Please check if the DOMAIN\DomainAdmin has full permission to access to the file "Y:\FILE5945031821.txt".

    2.May I know if you can create a text file on the "Y" drive manually?

    3.Please Create a SQL Server Agent Proxy to use the user in design interface to execute ssis package.
    A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

    Best regards,
    Mona

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

    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.

    0 comments