I'm executing a package w/ a File System task that moves a file from an ftp server to a staging server. The package works fine from my desktop, 32bit, with my credentials even though it's using a share as source (\\server\ftpshare\file.csv) and a UNC path for the target (\\dbserver\c$\staging\file.csv) .

When the SQL Agent runs it, I get:

FSYS - move Src to Tgt,...,0,(null),Failed to move file with error 'Access to the path is denied.'.

I've verified that my sql agent acct (a low level domain account) has full control to both the source and target folders.

I've logged onto the db server as the sql agent and can move the file back and forth from the ftp share to the local drive via the GUI and thru a .bat script.

I created a credential for my domain account, set the step to Run As under that Proxy account and it works.

The server where this is running is a Server 2008 R2 x64, SQL 2008 SP1.

Any ideas what else it might be? I can't even tell if the agent is failing on the Source or Target path.

Thanks...

It is definitely a permissions issue. You can use a test package a try just renaming the file at the source and then at the destination to test to see if it is the source or the destinatio permission issue.

Thanks for the clever recommendation - I now know that my package is having issues w/ the Target/destination.

I changed the TgtPath variable from: "\\dbserver\c$\staging" to "C:\staging" (no quotes in the variable definition) and it was able to rename the file to the value of the TgtPathFileName variable expression. So, it looks like the SQL Agent has issues referencing a UNC path which is the preferred method of accessing files (read your post on Variable Best Practices).

 Could it be an issue with the UNC path pointing to the same server the package is running on? I really don't want to run the package w/ a proxy OR leave it hardcoded to "C:\...".

I was able to get it working by changing the variable "TgtPath" from \\dbserver\c$\staging to \\dbserver\staging. Since my SQL Agent runs under a plain vanilla domain account which is not in the Administrator's group, the agent can't use the Administrative share "C$".

However, I can't explain why when I am signed in as the sql agent account on the db server, I can use C$ both in windows explorer and in batch files (e.g this works: "move \\ftpserver\FTP\file.txt \\dbserver\c$\staging\file.txt"). I'll just declare victory and move on.

Thanks for the assistance, Mike!