Hi-
I have seen several problems posted where an SSIS package writes a file successfully when executed manually, but fails when executed via SQL Agent job..I have the opposite problem. I'll try to lay it out succinctly:
SSIS writes to a file on a shared folder, specified as \\HostA\Share, for example. I created the share and gave full control to Everyone (out of frustration).
I'm working from HostA via RDP, connected to the DB on DBHost via SSMS.
If I kick off a SQL Agent job that executes the package, it works fine. (SQL job is running under SQL Server Agent Service Account)
If I execute the job interactively (logged in to SSMS with Windows Auth), it fails with "Error: Cannot open the datafile "\\HostA\Share\filename.ext""
We did find that if I RDP directly to the DBHost, I am able to execute manually.
Also, if I try executing an xp_command shell command to write a file to the share, it works. (When RDP's into HostA with an SSMS connection to DBHost under my windows domain auth, as above.)
The problem is the same when I RDP to any remote host.
IT and SQL Admins are scratching their heads over this, any suggestions welcome.
thanks
-m