none
Flat file connection: The file name "\server\share\path\file.txt" specified in the connection was not valid RRS feed

  • Question

  • I'm trying to execute a SSIS package via SQL agent with a flat file source - however it fails with Code: 0xC001401E The file name "\server\share\path\file.txt" specified in the connection was not valid.

    It appears that the problem is with the rights of the user that's running the package (it's a proxy account). If I use a higher-privelege account (domain admin) to run the package it completes successfully. But this is not a long-term solution, and I can't see a reason why the user doesn't have rights to the file. The effective permissions of the file and parent folder both give the user full control. The user has full control over the share as well. The user can access the file (copy, etc) outside the SSIS package.

    Running the package manually via DTExec gives me the same error - I've tried 32 and 64bit versions with the same result. But running as a domain admin works correctly every time.

    I feel like I've been beating my head against a brick wall on this one... Is there some sort of magic permissions, file or otherwise, that are required to use a flat file target in an SSIS package?

    Friday, March 28, 2014 3:01 AM

Answers

  • I suspect that your permission settings on the folders are not absolutely as you said above.

    Thanks Mike,

    You're spot on - I've been changing so much stuff that I confused myself. Sorry! I did not have read access to the top level folder.

    The other thing that totally confused me is that BIDS gives different results than when running via DTSExec or SQL Job Agent.

    BIDS will not work unless there is permission along the whole tree (this is what confused me!).

    DTSExec does not require permission along the whole tree - and I've narrowed it down to requiring at least the "List folder / read data" permission at the top level folder (\\server\share) in the share (I'm not talking share level permissions). My guess is that it is trying to determine that the share is available by looking at the top level contents.

    So in answer to my original question:

    Is there some sort of magic permissions, file or otherwise, that are required to use a flat file target in an SSIS package?

    Yes. "List folder / read data", at the top level of the share - and that's a bug.

    • Marked as answer by rossco150 Tuesday, April 8, 2014 2:32 AM
    Friday, April 4, 2014 4:11 AM

All replies

  • Correct the path as,

    \\server\share\path\file.txt


    Regards, RSingh

    Friday, March 28, 2014 3:52 AM
  • Are you harcoding the path or is it getting created through an expression/variable? I feel its  latter in which case you need to use it as below within expression

    "\\\\server\\share\\path\\file.txt"


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, March 28, 2014 5:32 AM
  • Sorry - typo when I stripped out the identifying information for our server name. It already is \\server\share\path\file.txt

    And the path is hardcoded - and works when it's run as a domain admin user, so the setup seems correct. I can only guess it's a permission error.

    Tuesday, April 1, 2014 10:14 PM
  • Agreed that it sounds like a permissions issue, however you stated it failed when you debugged locally.  Can you confirm your permissions to the file and share?  I'd first attempt to get the package working on your local machine (in BIDS).  
    Tuesday, April 1, 2014 11:10 PM
  • Another question after re-reading...is this flat file a source in a data flow task, or is it a destination?  Is it being referenced anywhere else in the SSIS package (ie being created/moved in one step, then read from in another)?

    ...also, 32 bit or 64 bit shouldn't make a difference when working with a flat file.  

    Tuesday, April 1, 2014 11:34 PM
  • Another question after re-reading...is this flat file a source in a data flow task, or is it a destination?  Is it being referenced anywhere else in the SSIS package (ie being created/moved in one step, then read from in another)?

    ...also, 32 bit or 64 bit shouldn't make a difference when working with a flat file.  

    Flat file connection can be used as a source or destination. It depends on your implementation.

    Can you access the shared drive "\\server\share\path" from you system ?


    Regards, RSingh

    Wednesday, April 2, 2014 5:39 AM
  • Ok, so I'm on to something now, and it seems like a bug to me.

    The relevant permissions are:

    \\server\share\ - top level folder - read only
    \\server\share\path1\ - parent folder - no access
    \\server\share\path1\path2\- Folder level - full control
    \\server\share\path1\path2\file.txt - file level - full control

    If I grant read access to \\server\share\path1\ then the job succeeds. So why does the job need to enumerate through each directory in the whole path? Surely this is a bug? Principle of least privilege and all that, I shouldn't need to give rights to the parent folders.
    Thursday, April 3, 2014 3:22 AM
  • In order to access child folder you need to have parent folder access. Therefore in the "Security" tab properties of the parent folder, if you click the advance button a check mark should be there in "Include Inheritable permission from the object's parent".

    Regards, RSingh

    Thursday, April 3, 2014 3:55 AM
  • In order to access child folder you need to have parent folder access.
    That's purely a SSIS limitation, not a NTFS or CIFS limitation, and I would say that it's a bug. There are many good reasons not to inherit permissions (depending on the circumstances).
    Thursday, April 3, 2014 4:15 AM
  • In order to access child folder you need to have parent folder access.

    That's purely a SSIS limitation, not a NTFS or CIFS limitation, and I would say that it's a bug. There are many good reasons not to inherit permissions (depending on the circumstances).
    How have you setup package? Have you directly set full path for the file and are you using a file enumerator to iterate through files in folder?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, April 3, 2014 4:52 AM
  • In order to access child folder you need to have parent folder access.
    That's purely a SSIS limitation, not a NTFS or CIFS limitation, and I would say that it's a bug. There are many good reasons not to inherit permissions (depending on the circumstances).
    You can not blame SSIS when you do not have the right permission to access items within the folder. When you do not have access to \\server\share\path1\ are you expecting to use files located under this folder ?

    Regards, RSingh

    Thursday, April 3, 2014 5:00 AM
  • How have you setup package? Have you directly set full path for the file and are you using a file enumerator to iterate through files in folder?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    The package is quite basic - the path to the full file is staticly defined in the connection string. I'm not enumerating through anything directly, not trying to do anything that clever!
    Thursday, April 3, 2014 5:08 AM
  • When you do not have access to \\server\share\path1\ are you expecting to use files located under this folder ?

    Yes I am. I can read/delete/modify the file via the filesystem or any other application (eg notepad), so I expect SSIS to behave the same way.

    What makes you think that you need access to the parent folder to access a subfolder? Just because you can't click and browse via Explorer doesn't mean you can't access the file. Just typing the full path into explorer address bar works, for a start.

    Thursday, April 3, 2014 5:35 AM
  • Hi Rossco150,

    I have tried to reproduce the issue in my test environment (Windows Server 2012 R2 + SQL Server 2008 R2), however, everything goes well with the permission settings as you mentioned. In my test, the permissions of the folders are set as follows:

    \\ServerName\Temp  --- Read
    \\ServerName\Temp\Source  --- No access
    \\ServerName\Temp\Source\Flat Files --- Full control

    I suspect that your permission settings on the folders are not absolutely as you said above. Could you double check the permission settings on each level of the folder hierarchy? In addition, check the “Execute as user” information from job history to make sure the job was running in the proxy security context indeed. Which version of SSIS are you using? If possible, I suggest that you install the latest Service Pack for you SQL Server or even install the latest CU patch. 

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support


    Friday, April 4, 2014 3:17 AM
    Moderator
  • I suspect that your permission settings on the folders are not absolutely as you said above.

    Thanks Mike,

    You're spot on - I've been changing so much stuff that I confused myself. Sorry! I did not have read access to the top level folder.

    The other thing that totally confused me is that BIDS gives different results than when running via DTSExec or SQL Job Agent.

    BIDS will not work unless there is permission along the whole tree (this is what confused me!).

    DTSExec does not require permission along the whole tree - and I've narrowed it down to requiring at least the "List folder / read data" permission at the top level folder (\\server\share) in the share (I'm not talking share level permissions). My guess is that it is trying to determine that the share is available by looking at the top level contents.

    So in answer to my original question:

    Is there some sort of magic permissions, file or otherwise, that are required to use a flat file target in an SSIS package?

    Yes. "List folder / read data", at the top level of the share - and that's a bug.

    • Marked as answer by rossco150 Tuesday, April 8, 2014 2:32 AM
    Friday, April 4, 2014 4:11 AM
  • Hi Rossco150,

    Thank you for your posting.

    You’re right that SSIS requires the proxy to have access to the top level shared folder. I am able to reproduce the issue when remove the access to \\ServeName\Temp – the top level share folder for the user. Based on my test, the minimal right required on the top level shared folder to run the package is “Read”. Besides, when running the package in BIDS, we don’t need to grant permissions for the user on the mid-level folder (folder path1 in your example).

    I am not sure whether it is a bug or a by design behavior. Here, I would suggest you submitting a wish at https://connect.microsoft.com/sql.

    Connect site is a connection point between you and Microsoft, and ultimately the larger community. Your feedback enables Microsoft to make software and services the best that they can be, and you can learn about and contribute to exciting projects.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Friday, April 4, 2014 5:14 AM
    Moderator