I'm running a program on my local machine ("POS3" is the name) and I'm importing data from a CSV file on a shared network drive (S:\) using an SSIS package on the shared network drive (S:\) by running an SQL Agent Job on the local MS SQL server (on the local "POS3" machine).
I'm using an SQL Agent Job to execute the package because only the local "POS3" server has Integrated Services installed.
The error I get is as follows:
Date 8/23/2011 11:12:20 AM
Log Job History (ImportData)
Step ID 1
Job Name ImportData
Step Name Load History
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
Executed as user: POS3\SYSTEM. Microsoft ® SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright © Microsoft Corp 1984-2005. All rights reserved. Started: 11:12:20 AM Error: 2011-08-23 11:12:21.28 Code: 0xC020200E Source: Data Flow Task 1 Source - ITEMHIST_CSV  Description: Cannot open the datafile "S:\POS3\ITEMHIST.CSV". End Error Error: 2011-08-23 11:12:21.29 Code: 0xC004701A Source: Data Flow Task 1 SSIS.Pipeline Description: component "Source - ITEMHIST_CSV" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:12:20 AM Finished: 11:12:21 AM Elapsed: 0.86 seconds. The package execution failed. The step failed.
If I run the package in Management Studio, it loads just fine. It's only when the package is called by the Job that it fails. I am assuming it's a permissions issue, but I've tried everything I could find on the web about setting up the permissions to allow everything to run smoothly and it still fails.
Any suggestions? I know you guys are more experienced with this than I am.