none
Problem with executing an SSIS package using data stored on a shared drive

    Question

  • This is a fairly perplexing problem for me.  We have a application that writes files out to a shared drive that I need to process each day.  We run SQL Server 2008 R2 in a 64 bit environment.  My script runs fine in BIDS but when I try to run it with SQL Agent I get the message :

    An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "'X:\20121001\' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".

    I've tried using the UNC path and everything else that I can think of.  The SQL Agent job runs under a Proxy Account which uses my credentials.  Do I need to give the Proxy Account some additional privileges? 

    The thing that I can't figure out is why the path is marked as invalid when it runs in SQL Agent but not BIDS. I've been working on this all day and would like to put this thing to rest!


    Harold Jackson

    Tuesday, September 24, 2013 12:26 AM

All replies

  • The user account under which you run the packages should have permissions to access the path you have given.

    Just give a try by giving X:\\20121001\\

    Thanks,

    Anilkumar

    Tuesday, September 24, 2013 6:14 AM
  • Is your Database server(where SQL agent job is runing) is different than your developmenet environment(Where you run the package from BIDS). If the answer is yes, please give the network path like \\.... instead of X:\\..

    Tuesday, September 24, 2013 8:38 AM
  • HI Harold,

    It Looks like you have mapped the shared drive as local drive in you my computer. Hence, you are getting Drive Name to Shared Folder (As X:\). But you will not able to access the this folder using the mapped Drive letter in your SSIS in SQL Agent.

    try changing the X:\ to your actual shared drive path.

    Please make sure below

    1) File Sharing port are open between DB server and Shared Drive
    2) Account in SQL Agent is running as access to Shared Drive or Folder 


    Naveen Kumar

    • Proposed as answer by Harry Bal Tuesday, September 24, 2013 7:50 PM
    • Unproposed as answer by Shannon Wade Wednesday, September 25, 2013 12:29 AM
    Tuesday, September 24, 2013 10:34 AM
  • If your package runs fine in BIDS (SSDT/VS), but not as a job (or a job via a stored procedure) then it's probably an user/right issue.

    The job isn't running with your account (like in BIDS). In the jobstep you can see which account is used the run the package: a proxy or the default SQL Server Agent Service Account.

    Either give the account used enough rights or create an user, credential and proxy with the appropiate rights. What you could try is to use your account in the credential/proxy.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Tuesday, September 24, 2013 10:46 AM
    Moderator
  • I'll try that because that is probably the one thing that I haven't tried.  I've used the fully qualified UNC path

    \\xxx.xxx.xxx.xx\D$\foldername\20121001 and every variant of that to no avail. 

    The one thing that I neglected to mention yesterday is that I'm going across networks.  That is, everything that I'm running is on the main corporate network and the files that I am processing are on a separate network. 

    We have a username and password that we used to establish the share.   I have created a SQL login for that user, and made it a part of the sysadmin group.  That should have made it part of the proxy account used to execute the job.  Did I miss something here? Or is the issue with something else?


    Harold Jackson

    Tuesday, September 24, 2013 5:55 PM
  • Thanks for your response.  That is exactly what I did a short while ago and I run all SQL Agent jobs related to SSIS using that account.  All other jobs run just fine except this one.  I think that the issue is related to the fact that I'm going across networks to process the files in the SSIS job.  Do you have any suggestions as to what I should be on the look out for when crossing networks? 

    Harold Jackson

    Tuesday, September 24, 2013 6:02 PM
  • My first reply was to Anilkumar.  Just wanted to clear that up.

    Harold Jackson

    Tuesday, September 24, 2013 6:06 PM
  • I'll try that because that is probably the one thing that I haven't tried.  I've used the fully qualified UNC path

    \\xxx.xxx.xxx.xx\D$\foldername\20121001 and every variant of that to no avail

    Fully qualified UNC path should work in SSIS package. Did you tested the UNC path as \\servername\folder\abc.txt ? wihout the $ sign.

    If testing locally your id should have access to the shared network path..if it is successful, then the SQL agent acct should have same permissions on the share as your id to run the pkg


    Thanks, hsbal

    Tuesday, September 24, 2013 7:55 PM
  • Thanks Harry but I've tried that as well.  Do you know of any special conditions when using shares that are not part of your local network?


    Harold Jackson

    Wednesday, September 25, 2013 12:34 AM
  • Hi Harlod,

    Did you find the solution for the problem?

    Thanks,

    Anilkumar

    Wednesday, September 25, 2013 1:18 PM
  • Hi Harlod,

    Did you find the solution for the problem?

    Thanks,

    Anilkumar


    Thanks for asking but I have not found an answer for the problem in the solution space that I was working.  I'm going to try a couple of other things and, if they don't work, go to an alternative.  My Plan B will be to move the directories over to the server that runs SSMS and SSIS and process the files from there.  Not as elegant but should be maintainable.

    Harold Jackson

    Wednesday, September 25, 2013 5:21 PM