I spent another day tearing my hair out yesterday trying to resolve an issue with SSIS packages runnning in SQLAgent (not got much left at the moment, maybe I should contact the SSIS team for a wig).

My situation was that I am deploying packages to a development server, and to provide isolation I was running jobs with a proxy account that only had access to the development servers. Proxies are an awesome feature and mean that you should never have to "just run the job as sysadmin".

The issue I was facing was that the job step was failing. The job step was a simple execution of the package.The following errors appeared in my log file. I always check the "Log step output in history" for a job step, this ensures you get all the output from the command that you run. I'll blog about this later.

If looking at the output in sysdtslog90 then you will have an entry with datacode -1073573533 and error message

File or directory "<filename>" represented by connection "<connection>" does not exist. 

Not exactly helpful.

If you get the output from the console then you will also get these errors.

0xC0202070 "The file name property is not valid. The file name is a device or contains invalid characters."

0xC001401E "specified in the connection was not valid."

It appears this error is due to the use of a UNC path and the account runnnig the package not having access to all the folders in the path.

Solution

To solve this you need to ensure that the proxy account has access to ALL folders in the path you are accessing.

To check this works, logon as the relevant proxy user, or run a command window as the specified user. Then try and do

net use \\server\share

and then do a dir for each folder in the path and check you have access.

If these work and you still have the problem then you have some other problem, sorry.

The following are posts on experts exchange that also discuss this,
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24056047.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23968903.html

This blog had a post about it being a 64 bit issue. That definitely wasn't the issue for me as I was on a 32 bit server http://blogs.perkinsconsulting.com/post/64-bit-SQL-Server-2005-SSIS-and-UNC-paths-Part-2.aspx