Post reply

SQL Server 2005 SSIS Package that writes to a file runs through MSDB, but not through a job

  • September 23, 2009 at 10:46 am

    #138999

    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. 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.

  • September 23, 2009 at 10:57 am

    #1057653

    It's a permissions issue. The account that the SQL Server Agent runs under doesn't have access to that location.

  • September 23, 2009 at 11:07 am

    #1057660

    I thought so too, but even if I run the step under a proxy that does have permissions to the server\filepath, it still won't work.

    Any other ideas?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply