Fri 24 Oct 2008
In short, avoid using mapped network drives inside your SSIS package for things like file paths, control flow settings, log file locations, etc… Instead use UNC path notations. (\\server\share\directory)
Why? Well, if you’re always running an SSIS package by hand using dtexec.exe, or through Business Intelligence Development Studio (BIDS), dtexecui.exe, etc… you don’t really have to worry about this. However, if you’re running the package via a scheduler such as SQL Agent, you’ll run into perceived permission problems, or reports that files cannot be found. The issue with schedulers such as Agent is that they run under a “service account” that does not have access to mapped network drives.
Mapped network drives are set up for your user-session when you log on to a computer. Even if you log in with the same account that the SQL Agent service uses, the service will still not have access to any drives you map because the service does not have access to the drives in *your* session.
This is a common issue that I see with users who are trying to repoint the default behavior of SSIS to a network share, or that are trying to read/write files on a share.
November 2nd, 2008 at 5:19 am
ihave agree
December 15th, 2008 at 11:36 am
Hi Phil,
I’ve encountered the exact problem you’ve described but even using the UNC path has not helped. Is there somethin else I might be missing like a specific package proctection level or something?
December 15th, 2008 at 1:19 pm
What errors are you receiving, Adam? Are you running the package through SQL Server Agent, or another scheduling tool?
Remember that just because you might have access to a network resource doesn’t mean that the account that executes the package does.
Phil
December 18th, 2008 at 10:04 am
I actually dealt with this issue today.
Remember that if you execute the package “in context of the server” thus via SQL Agent (scheduled job) you need to give the account under which the SQL Agent is running access to the folders where you are trying to access files/data.
December 18th, 2008 at 2:53 pm
In the end I did exactly that, I setup some sql security credentials, then created a proxy under SSIS Package Execution which I gave the SQL Agent to run as when calling my pacakage.
Thanks for the all your responses and the acticle.
April 28th, 2010 at 11:29 am
This was my problem. Thanks for the solution.