+ Reply to Thread
Results 1 to 2 of 2

"Cannot open the datafile" error running SSIS package from SQL Age

  1. "Cannot open the datafile" error running SSIS package from SQL Age

    Environment
    OS: W2K3 SP2 R2 x64 both Ent & Std ed
    SQL: 2005 SP2 x64 Ent & Std ed
    Services logons: SQL Server & SQL Server Agent as ourdomain\sqlsrvlogin
    Server login: ourdomain\srvadmin
    Perms: ourdomain\sqlsrvlogin & ourdomain\srvadmin members of local
    Administrators group

    Problem

    An SSIS pkg that does a simple query (select * from tbl) and exports that to
    a CSV file (c:\myfile.csv) fails when run as a job from SQL Server Agent but
    runs fine when executed from SSIS\Stored Packages\MSDB or from BIDS. The
    error displayed when run from SQL Server Agent is:

    Executed as user: ourdomain\sqlsrvlogin. Microsoft (R) SQL Server Execute
    Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft
    Corp 1984-2005. All rights reserved. Started: 2:50:34 PM Error:
    2007-07-11 14:50:42.39 Code: 0xC020200E Source: Data Flow Task
    Destination - MyFile_CSV [40] Description: Cannot open the datafile
    "C:\MyFile.CSV". End Error Error: 2007-07-11 14:50:42.39 Code:
    0xC004701A Source: Data Flow Task DTS.Pipeline Description: component
    "Destination - MyFile_CSV" (40) failed the pre-execute phase and returned
    error code 0xC020200E. End Error DTExec: The package execution returned
    DTSER_FAILURE (1). Started: 2:50:34 PM Finished: 2:50:42 PM Elapsed:
    7.984 seconds. The package execution failed. The step failed.

    Troubleshooting

    I confirmed ourdomain\sqlsvrlogin has full perms to c:\ by viewing its
    Effective Permissions and by logging in as sqlsvrlogin and creating a file in
    c:\. If I run the following command at the server, I can save the new
    notepad file to c:\ with the name MyFile.CSV no problems.

    runas /userurdomain\sqlsvrlogin notepad

    If I explicitly add ourdomain\sqlsvrlogin with modify perms to c:\ and run
    the job, it works.

    Is this a bug or am I missing something in the permissions needed for SQL
    Server Agent to write to the local filesystem?

    Thanks :)

  2. Re: "Cannot open the datafile" error running SSIS package from SQL Age

    You might want to check the "ownership" of the SQL Agent Job.
    In SQL Server Mgt Studio go to the Object Explorer, expand SQL Agent and locate the job. Select the job, right click and show properties. Then on the Job Properties see which user is listed as the "Owner". That's the user that needs permission to the filestore.

+ Reply to Thread