"Cannot open the datafile" error running SSIS package from SQL Age
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
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  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.
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?
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.