SQL Server 2005 SSIS job that writes a file to another server will not run, but will run through MSDB

Question

I have a SSIS package that writes to a csv file on another server. It will run fine when I execute it manually out of the MSDB folder, but when I try to run it through a job, it fails with the following errors:

If I run it with the file path as W:\share\file.csv (I have the other server mapped to the drive letter W:), I get:

Executed as user: NT AUTHORITY\NETWORK SERVICE. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:28:34 PM Error: 2009-09-22 15:28:36.65 Code: 0xC020200E Source: Data Flow Task Description: Cannot open the datafile " W:\share\file.csv ". End Error Error: 2009-09-22 15:28:36.67 Code: 0xC004701A Source: Data Flow Task DTS.Pipeline Description: component "FlatFileConnection" (46) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:28:34 PM Finished: 3:28:36 PM Elapsed: 2.484 seconds. The package execution failed. The step failed.

If I run it with the file path as \\server\share\file.csv, I get:

Executed as user: NT AUTHORITY\NETWORK SERVICE. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:28:44 AM Error: 2009-09-23 09:28:45.62 Code: 0xC001401E Source: Connection manager "Flat File Connection Manager" Description: The file name \server\share\file.csv " specified in the connection was not valid. End Error Error: 2009-09-23 09:28:45.62 Code: 0xC001401D Source: SSIS Job Description: Connection "FlatFile Connection" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:28:44 AM Finished: 9:28:45 AM Elapsed: 0.813 seconds. The package execution failed. The step failed.

I can run it successfully using both of these file formats if I run it manually through the MSDB folder. I have tried using different proxies besides NT AUTHORITY\NETWORK SERVICE, such as the system and network administrator, but still no luck, so I really don’t think it’s a permissions issue. The job will run successfully if I try to write the file to the local server.

Any ideas? I have been struggling with this one for a while so I would sure appreciate some good feedback on this.

Answer 1

It really seems like a permissions issue. When you execute it manually it's running as your network account. When you execute it from a job it's running as NT AUTHORITY\NETWORK SERVICE. Try changing your SQL Agent service account to run as you, i.e., your network account. Then let it execute the job running under your credentials (make sure you start and stop the service). This will verify if it is really a permissions issue.