SSIS - Access Denied with UNC paths - The file name is a device or contains invalid characters
- by simonsabin
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.htmlhttp://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
