SSIS package won't run as SQL Agent job

Question

Hello,

I have an SSIS package that writes the results of a query to a flat file.  The package runs within Visual Studio, it runs when deployed to the MSDB database and I run the package, but when I schedule it in a sql agent job it fails with the following error message

Started:  5:02:00 PM  Error: 2013-01-09 17:02:02.12     Code: 0xC020200E     Source: Data Flow Task Flat File Destination [551]     Description: Cannot open the datafile "R:\XXXXXX\FinalReports\BRICLast24Hours.txt".  End Error  Error: 2013-01-09 17:02:02.13     Code: 0xC004701A     Source: Data Flow Task SSIS.Pipeline     Description: component "Flat File Destination" (551) failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:02:00 PM  Finished: 5:02:02 PM  Elapsed:  1.625 seconds.  The package execution failed.  The step failed.,00:00:02,0,0,,,,0

I've granted full control permissions to this txt file to the sql agent service account and my windows account.  The job step that runs the ssis package is run under a proxy I created that uses my windows credentials. I have administrator privileges on the server I run the job from.  I tried a UNC path rather than a drive letter.  I can't think of much more I can try to get this to run as scheduled.  Any info would be greatly appreciated.

Thanks in advance.

Answer 1

You are going to have to use the UNC path.

When you log in to the server on which you are running the SSIS package using your login, can you create a file in the folder? Is the share set up right?  My understanding is that you can share a folder with read only rights and that will block writing to that folder.

Also check the permissions on the folder itself. I am no security expert, so I may be advocating too much permission, but you may need to give write access to the folder as well as the file. 

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

Agreed, when you login, your login scripts kick in to map drives.

Scheduling it via sql agent, no login scripts are executed. You must use unc path. Ensure agent account has proper share/NTFS permissions to the unc path.