locked
Accessing mapped network drive RRS feed

  • Question

  •  

    I'm running SQL Server 2005 (9.00.3282) on Windows Server 2003.  I have an SSIS package that ran perfectly fine until I moved it to a new server.  Now it works when I run it manually, but it does not work while running from SQL Server Agent.

     

    The problem is that I cannot access files on a network share mapped to a drive letter.  For example, the package needs to access \\server\apps\export.exe.  I can access it within the package just fine if I refer to the UNC path, but if I map \\server\apps to F:, I cannot access F:\export.exe. 

     

    Unfortunately I can't just recode the package to use UNC paths, because export.exe is an ancient dos-based app that only understands drive letters.

     

    I'm certain this is not an SSIS problem, but it has something to do with how the agent is running the package.  I suspect that the agent is not mapping the drive.  The agent runs as a user called SQLAGENT, and I've created a drive mapping for that account by logging in and running "net use /persistent".  I've tried adding a batch job at the beginning of the package that executes a "net use" to map the drive, but to no avail.

     

    When I log into the server as SQLAGENT, and run the package from within Visual Studio, it runs just fine.  It's only when I try to start the job from management studio or on a schedule that it fails.

     

    Any suggestions to help figure out why the drive isn't being mapped correctly? 

     

    Again, this worked on a previous server, but I can't for the life of me figure out what I might have missed setting up this server.

     

    Lee Silverman 

    Wednesday, October 15, 2008 2:03 PM

Answers

  • I must have done something wrong in my earlier attempts to get this working.  Last night I was able to fix the problem by creating a batch job that maps the drive letter.  (For the benefit of anyone who finds this thread, just create a text file with the line "net use F: \\server\whatever" and name it something.bat.)  In my case, I set up my SSIS job to run that batch as the first task in the control flow.  After that, everything runs smoothly.  Even subsequent steps in the job can access the mapped network drive just fine.  I believe you could also run that job as the first "step" in your SQL Agent job, and it would also work.

     

    Lee

     

    Monday, October 20, 2008 2:20 PM

All replies

  • it is an SSIS thing. you must give unc path while running packages using sql agent.

     

    Wednesday, October 15, 2008 2:18 PM
  • It'snot an "SSIS thing".  SSIS packages are run as services - the account you are using doesn't actually "log on" to the system, and doesn't run the startup processes that would normally accompany a logon.  Imagine if you put your account in instead, and it fired up explorer, a display adapter control program, Office's launch optimizer, and whatever other desktop apps happen to be configured for your account... Smile  Not pretty.

     

    Wednesday, October 15, 2008 2:59 PM
  • I agree with Todd, it's not an SSIS thing, especially because it worked on the previous server.

     

    So if the worker account does not actually log on or run the usual logon processes, how do I get it to map a drive?  Again, I tried inserting a batch program as the first step in the package that ran "net use F: \\server\apps" before any other steps, and that didn't seem to work.  Any other suggestions?

     

    Lee

    Wednesday, October 15, 2008 6:25 PM
  • Nitin's post is correct - use the UNC path in SSIS - I was just explaining "why"

     

    Wednesday, October 15, 2008 6:31 PM
  • Sorry to confuse you guys, what i ment was, its hapenning not because of any other reasons (wrong mapping etc) but because this is what sql agent service expects the path to be.

    And lee, why do u think you need to change your package to cater for unc paths, as the configurations should be independent of the package...

    Thursday, October 16, 2008 10:23 AM
  • All,

    As I said in my original post, this SSIS package worked on another server.  When I moved it to this server, it stopped working.  And as I also said in my original post, while I could change the SSIS package to use UNC paths, export.exe is going to be looking for files on the network drive, and I can't change export.exe.  I need to get the network drive working.

    Somehow I did something on the other server that enabled the SQL Agent worker account to map the appropriate drive letters before running the package.  I just can't for the life of me figure out what I did.

    I've tried going into SQL Agent and having the job run under my account and under the domain admin account.  No luck either way.

    Any suggestions on what I did on the other server to map the network drive for the worker account?

    Lee
    Thursday, October 16, 2008 11:31 AM
  • Lee,

     

    I have run into similar issues when creating SSIS packages with one account (has local and network admin privileges on all machines that I work on developing the package) and it runs fine within the BIDS environment, but when I try to schedule it to run with SQL Agent, it bombs.  I can't offer anything helpful regarding the UNC vs. mapped drive issue, but here are two articles that helped me immensely.

     

    http://support.microsoft.com/kb/918760

     

    http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/

     

    By the way, what I do now is use a "generic" logon to do all development work, which is the same account that starts and runs the SQL Agent service on both DEV and PROD SQL Servers.  The articles (above) recommend changing the Encryption settings--but I've found that problematic as well.

    Thursday, October 16, 2008 1:33 PM
  • Lee,

    Can you check how did you run the package on the other server? Did you run using proxy account or you run it using the default SQL Job Agent account? If it is the second, can you check under what account is your SQL Job Agent setup to run?
    Thursday, October 16, 2008 3:42 PM
  • I must have done something wrong in my earlier attempts to get this working.  Last night I was able to fix the problem by creating a batch job that maps the drive letter.  (For the benefit of anyone who finds this thread, just create a text file with the line "net use F: \\server\whatever" and name it something.bat.)  In my case, I set up my SSIS job to run that batch as the first task in the control flow.  After that, everything runs smoothly.  Even subsequent steps in the job can access the mapped network drive just fine.  I believe you could also run that job as the first "step" in your SQL Agent job, and it would also work.

     

    Lee

     

    Monday, October 20, 2008 2:20 PM
  • Hi Lee,

      I got the very same problem.  I tried the same resolution, However, in the SQL Agent history log, it ask question as if we want to recover the connexion memory (Y/N)... so it failed...

      any idea to have the net use command pass?

      I use "NET USE w: \\servername\folder$ / persistent:yes"

     Al

     

     

    Sunday, May 24, 2009 2:03 PM