Basically I'm trying to run a functioning SSIS job (created in BIDS) under the SQL Server Agent – it reads a set of Excel spreadsheets and dumps the results into a table.
The problem I'm having is getting the SSA to read the file path, the relevant part of the error is:
"0x80004005 Description: "'N:\Assets Property & Facilities Management\Monthly Absence.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."
I've tried using UNC paths (\servername\ share) but the BIDS rewrites the paths to standard file paths (c:\directory\filename), I've also tried a proxy which runs this step under an Admin account. I've also tried changing the path to UNC on the SSIS job on the server. Also I'm forcing it to use the 32 bit DTEXEC, so it's not that either
Always get the same error, do I need to re-create the job from scratch?
Best Answer
Make sure the account running SQL Server agent has rights to the path specified. e.g. if SQL Server agent service is running under domain\account1 then domain\account1 has proper rights to access the file system(path specified in package).