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.

Answer 2

How is your W: mapped? Make sure it is a mapping that is available at the time it is used, for the account running the package. See MSDN blog entry here. Here is some relevant notes from that article:

As explained in KB180362 article this was precisely the problem with the Agent job:

A service (or any process that is running in a different security context) that must access a remote resource should use the Universal Naming Convention (UNC) name to access the resource. UNC names do not suffer from the limitations described in this article.

I was getting the same error, with SQL Server 2008 R2 running a job as sa and writing to same server. In my case, I changed the \server\dir\subdir path in my SSIS config file to e:\dir\subdir, which is what the local account would normally use. That seems to work. (My job HAD been running fine for a long time, and only recently started to fail, but not always.)