SSIS 2008 - Will not access UNC path from Job

  • Ok Ok I know you think this has been asked a million times before but just hear me out?

    The SQL Server Agent Account runs under a domain account which has permission on a network share. I have a package which reads in CSV files from this share. I can log onto the server as the SQL Server Agent Account and run the package (from the parent job onwards) in BIDS no problemo.

    As soon as I try to run the same package (in both 32 or 64 bit mode) this as a job it fails with this error "Description: The file name property is not valid. The file name is a device or contains invalid characters."

    What is going on??? I can access the same network share on this server when logged in as the agent account. This is a new one to me. Even making the account a domain admin as a test does not solve the issue. It must be something specific to the SQL Server Agent and how it executes jobs.

    It may be worth nothing that I've had this working in the same way on a different server for the same client.

    Any advice welcome!

  • What is the full UNC path + file name?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Here's an example of one of them

    \\bhdata1\eig\Central\QMS\Archive\qlextlivi4.csv

  • How are you passing the file names to the job? Is it hard-coded, or via some sort of config?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Yes, from a SQL Server Configuration table.

  • Everything sounds fine.

    Notwithstanding your comments, I think the next step should still be a check on permissions. Please try putting the file in

    c:\import

    (or wherever)

    and see whether you still get the error.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Yep, I've done that too.

    I copied the files onto the server, update the connection and it worked from the job fine. It just will not hit a UNC path from the job.

    I am at a loss.

  • Ok listen to this. If I set up a proxy account a link it to the SQL Server agent domain account it works?? Something is not quite right when the SQL Server agent account is used and it makes the leap from the local machine and onto the network

  • aaa-322853 (6/21/2011)


    Ok listen to this. If I set up a proxy account a link it to the SQL Server agent domain account it works?? Something is not quite right when the SQL Server agent account is used and it makes the leap from the local machine and onto the network

    Sounds bizarre, but I'll bet that you're happy to take that solution - not too much of a hack.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • No so happy as this is in the UAT environment, it somehow worked ok in the dev environment and who knows what might happen in production!

    I will continue to work to get to the bottom of it and post a resolution on here (if I get one!)

  • Strange.

    Any difference if you move higher up in the path?

    \\bhdata1\qlextlivi4.csv

    Any shares in the path?

  • It's been a while, but I ran in to the same issue - the service account had full rights to the folder, but couldn't access the file. After digging for far too long, I found that one of the folders in the directory structure was inaccessible by the service account. Example:

    \\Server\Folder1\Folder2\Folder3\File.txt

    In my case, the root folder (Folder3) allowed the service account full permissions, but one of the uplevel folders did not. When I granted the service account access to that parent folder, everything worked as expected.

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • That is good to know. Thanks for posting a solution Tim.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply