none
UNC in SSIS

    Question

  • Hi,

    I have an SSIS 2005 task that takes txt files, on a network server, and processes them into a 2000 db.  Currently I have the path to the folder mapped on my local machine to a 'z-drive' and all works great.  When I try to access the location with the unc(\\servername\folder\folder\) I get errors like below.

    Connection manager "Sync Flat File Loads": The file name "\\server.domain.com\folder\folder\Filename20100908114108.txt" specified in the connection was not valid.
    Error: 0xC0017004 at XXXXXXX_Operation: The result of the expression "@[User::strToProcessFolder]  + @[User::strFileName]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    I have tried to directly set the full path to the server\folder

    I have tried using the full \\server.domain.com\folder\folded with the same errors resulting(another blog on this site). 

    I have looked at the sever path in Explorer, as suggested in another question on this forum, and have the same errors. 

    In addition, I can copy the full path and file name in the 'run' cmd and the file opens as expected (\\server\folder\Folder\Filename_20100908114108.txt)

    Any ideas what might be causing the issue?

    thanks

    tt

    Wednesday, September 08, 2010 6:24 PM

Answers

All replies

  • Hello, 

    provide UNC like this

    \\YOURSEVERNAME\D$\FOLDER\

     

    --See Here i provided Drive name with Dollar sign after server name.

    Hopefully it will work for you.

    Thanks

    Wednesday, September 08, 2010 6:26 PM
  • Hello,

    Thanks for the reply but it does not appear to solve the issue.

    tt

    Wednesday, September 08, 2010 6:42 PM
  • Hi,

    I recently discovered a bug (confirmed by Microsoft) that concerns the use of UNC paths in SSIS. I documented it here: 

    Be wary of using UNC paths in SSIS packages

     I don't know if you are experiencing the same issue or not but thought it may be worth sharing.

     

    -Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, September 08, 2010 6:48 PM
    Moderator
  • Hi Jamie,

    Your blogs are a 'staple' in my SSIS diet and yes I did see that post.  I have made adjustments you describe so I could set the path from "z:\" to "\\server\folder\folder" but no luck yet.

    thanks

    tim

     

    Wednesday, September 08, 2010 6:57 PM
  • I have the same problem, posted about it here and reported it to Microsoft.  I haven't heard anything and it is a show stoper for my groups use of SSIS going forward (we're considering SAS which actually does work and has for the last 30 years).  When I use a UNC with the flat file connection manager the service account needs full access to the directory path and all files within the directory.  If it doesn't have full access (say read only) then SSIS will report errors.  However, if I use a script component and .net to open up the file and process it then everything works fine.  Of course good OPSEC demands that accounts only be given the level of access needed to do the work.  It seems like SSIS flies in the face of Microsoft's best practices.
    Wednesday, September 08, 2010 6:59 PM
  • Even though I don't normally recommend it - UNCs should be used - can you issue a NET USE command in your Agent job just before executing the package, so that you can use a mapped drive?


    Todd McDermid's Blog Talk to me now on
    Wednesday, September 08, 2010 7:05 PM
    Moderator
  • Hello,

    Little confuse, Read Jamie's blog for UNC but seems like it is working for me, Maybe I am missing some point.

    What I did

    Steps:

    1--Have a file on server, Created a package , FileLocationVariable in Package.

    2--Made connection to file and then in properties , Expression, ConnectionString set to FileLocatioinVariable,So may Connection manger is using location from variable.

    In variable provided the path without \\, no UNC Z:\MyFolder\Test.txt (Z is may mapped drive that is mapped to \\MySERVER\D$)

    3--Ran the package, it ran succesffuly and loaded data.

    Now in variable I changed the path with UNC \\MYSERVERNAME\D$\MyFolder\Test.txt

    Ran my package and it worked as expected, I created another file Test1.txt, changed the value for variable and then executed package again and it worked again.

    Please guide if I am missing any point. I use UNC often in my package to write log files and create path for log files dynamically(Add TimeStamp etc).

    I am using SQL SERVER 2008 on Win 2k3 server.

    Thanks

    Wednesday, September 08, 2010 7:33 PM
  • Hello TimeT1,

    I read your first post again and again, Are you sure your expression are evaluting to correction path?

    Connection manager "Sync Flat File Loads": The file name "\\server.domain.com\folder\folder\Filename20100908114108.txt" specified in the connection was not valid.
    Error: 0xC0017004 at XXXXXXX_Operation: The result of the expression "@[User::strToProcessFolder]  + @[User::strFileName]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

     

    But look the path you have provided to open the file.

    "In addition, I can copy the full path and file name in the 'run' cmd and the file opens as expected (\\server\folder\Folder\Filename_20100908114108.txt)"

     

    In your error, your filename does not have underscore but when you open by CMD you are providing correct filename with underscore.

    Please check your expression those are creating FileName with location.

    Thanks


    Wednesday, September 08, 2010 7:38 PM
  • Hello,

    Yes, that is just me making typo mistakes to hide some details.  The '_' will be present in the file name.

    thanks

    Thursday, September 09, 2010 2:02 PM
  • Hi,

    I will talk with the site IT to see what they will allow. 

    thanks.

    Thursday, September 09, 2010 2:04 PM
  • hello,  i had familiar problem, i was trying to execute batch file which was stored remotely. sql server with agent on one server and batch file on other.

    The problem was when i was using UNC in my batch file path ( \\server\c$\folder\batch.cmd ) on ssis Execute process task.

    I found workaround here http://www.eggheadcafe.com/software/aspnet/34273760/ssis-package-hangs-when-running-simple-execute-process-task.aspx it sais that you have to add your remote server name in IE Local Intranet ( Internet explorer -> tools -> internet option->security->Local intranet->Sites )

    After doing this my sql agent job was able to execute SSIS which contained Execute process task with UNC file path

    I hope it will help you :)

    Wednesday, September 15, 2010 11:48 AM
  • On my local system, its not working.And, those who are working on production server, recomended not to do this.As it could be a security concern.Thanks.
    Wednesday, August 14, 2013 6:05 AM