Pakcage Error...How to read files on a different domain?

So did some troubleshooting on the my previous post -http://forums.microsoft.com/forums/ShowPost.aspx?PostID=272319&SiteID=1

Thanks to everyone who tried to help... So I got some insight into what is happening.

My package was having trouble reading files on a remote domain although I mapped the drives locally. This does not cause either running the package to failed either through directly running under VSS, or Execution Utility. But it failed when I try to schedule it through SQL server agent.

I wonder if it is the security context problem and asking for help of how to get around that issue.

So I have a for each file loop container that retrieve filenames on a remote domain which has a different security account then my local account. So how I got around that was to map that drive locally to a drive letter...example W:\

then I use W:\ as my path within my package. I was trying to figure out where I can declare the connection within my package specifically, but don't know if I could do that.

If I run this through VSS or exectuion utility, it works okay. I was able to see W:\ and all the files on that mapped drive and read in the data.

However, when I schedule it under SQL agent, it doesn't see any files then exited the package as success right the way, because it has nothing to do....

So is there a different between the security context on mapped drive between VSS and my SQL agent? If so...How do I get around that challenge?

BTW, the two domain do not have share accounts between each others. I had to specified and map the drives explicitly.

THANKS!
JON

When you map a drive it is for that session on that machine for that account. So unless you are the service itself, it will have no effect on SQL Server Agent.

Ideally you would sort out trusts between the domains, maybe mapped accounts or such like.

A slightly less elegant method is to map the drive programatically. You can do this with the "net use" command from the command line, or just wrap it in a batch file. You can specify credentials on the command as well. Note that you do not need to assign a drive letter, and I prefer not to as it eliminates the risk of drive letter clashes in what is after all an unattended environment. You just map and reference the drive using the UNC path only.

|||

So can you pelase clearify your comment please?

What do you mean by unless you are the service itself, it will have not effect on SQL server agent?

So I run SQL server agent using my domain logon account, which is the same account I used to logon to my session which mapped the drive.... However the drive is in a differnt domain.

I still unable to use the SQL server agent to work. It still doesn't see the content of the mapped drive... yet the package works through VSS or Excute Utility Manager.

Why is that? Also Any link or command help of this "net use" you suggested?

Thank you very much for your helps!
Jon

|||What is meant is that just because you mapped the drive during your desktop session, that drive mapping is not available from within the executing SSIS package. It is a different "session". The package itself would need to do its own drive mapping in order for it to gain access. Then when the package is complete, the drive will be unmapped. (Of course, you desktop session drive mapping will be unaffected throughout this.)|||That is indeed what I meant. "The package itself would need to do its own drive mapping in order for it to gain access", and to do this you can use the NET USE command.