I have a DTSX package that is called from a SQL Agent Job.
The DTSX package references a file at a fixed location (e.g. e:\mssql\myfile.txt).
On most machines, this location exists, but on some I have to manually map this (which is not a problem - I know a better solution would be to use package conifgurations to dynamically pull the file location, but this is not an option here - and anyway I'd like to understand what is going on)
I have set up the agent service to run as a specific user (e.g. myuser)
When I log on as this user and map the directory, then run the dtsx package directly, then all goes well.
When I run the package through a SQL Agent Job, the file cannot be found. If I add a command line job step to the agent job to map the drive:
net use e: \\svr\location
Then all works file also.
So what is going on in the backgound? How come the SQL Agent user requries the drive mapping even when I am logged in as this user.