THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Be wary of using UNC paths in SSIS packages

I have recently discovered what I believe to be a bug in SQL Server Integration Services (SSIS) 2008 and am taking the opportunity herein to warn about it so that nobody else gets stung by it.

The bug concerns the use of Uniform Naming Convention (UNC) paths inside a .dtsx package (i.e. paths that start with a “\\”). I have managed to reproduce a situation where a package will attempt to validate a File Connection Manager containing a UNC path even if the task(s) that use that Connection Manager have got DelayValidation=TRUE. In other words, the package may attempt to validate a Connection that will fail and this will cause errors in your package.

The first screenshot below shows the execution of my repro package. Notice:

  • When the package starts up @[User::FakeFileLocation] points to a non-existent drive "u:"

Also be aware of a few things that aren’t evident from the screenshot:

  • The ConnectionString of the "FILE" connection manager is set to @[User::FakeFileLocation]
  • The script task changes @[User::FakeFileLocation] to be the same as @[User::RealFileLocation]
  • The dataflow task, the connection manager & the package all have DelayValidation=TRUE

In this case everything works OK, no errors are thrown, everything is peachy.

!cid_image005_jpg@01CB3F9D

Now take a look at this second screenshot. It shows the same package as before but with one important difference, @[User::FakeFileLocation] has been changed from u:\NonExistentFile to \\u\NonExistentFile. Notice in the output that we have some errors because SSIS has attempted to validate that UNC path; it did not attempt to do so before the change.

!cid_image006_jpg@01CB3F9D

One other point of note is that these errors did not cause the package to stop executing however the error will still “bubble-up” to any calling package and will be interpreted as a package failure which is what makes this such a dangerous bug.

I have submitted the repro to Microsoft at https://connect.microsoft.com/SQLServer/feedback/details/585479/. No reply as yet.

@Jamiet

UPDATE: Microsoft have acknowledged the bug and have resolved to fix it in SQL11 (aka SQL Server Denali). See here for the following response:

We believe we found the cause of the problem. One of the system API we use to help resolve path can return different error codes depending on the network configurations and what are available.

We will fix the bug in Release 11 of SSIS.

Published Thursday, August 19, 2010 9:15 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gene said:

I find this hard to believe.  Your server was named "u"?  You do know how to use UNC paths - right?

August 19, 2010 6:11 PM
 

jamiet said:

Gene,

I'm not sure what you're driving at. No, the server was not named U. Nothing is named U. That's the point - U does not exist as a server or a drive or anything else.

-Jamie

August 19, 2010 6:24 PM
 

Todd McDermid said:

I could repro this as well - very odd behaviour...

August 20, 2010 1:41 AM
 

Koen said:

Interesting. I also ran into this bug this week, but I didn't pay much attention to it, as there were also permission issues. I'll keep in mind that it can go horribly wrong.

August 20, 2010 2:58 AM
 

Nachi said:

Hi,

I have some different kind of problem with both SSIS 2005 and 2008 that if we try to call SSIS package from store proccedure then in the filepath variable needs to end double blackslashes(\\).

Is anyone come across this kind of problems?

Thanks

Nachi

August 20, 2010 12:17 PM
 

Jim said:

I reproduced this also, interesting find

August 20, 2010 5:28 PM
 

Francisco Isla said:

Thanks for reporting this to the community, I have seen this before. A simple patch for this issue (that I have turned into a working practise for the time being) consists on all UNC paths for files actually referring to the "development" template file in any landing area.

August 21, 2010 5:54 AM
 

jamiet said:

francisco,

yeah that's a good idea. what i would like is a method of "nuking" all connection strings when the package gets deployed. much safer.

-jamie

August 21, 2010 6:36 AM
 

Jeff Allen said:

I don't quite understand Francisco's statement: "UNC paths for files actually referring to the "development" template file in any landing area" Can anyone explain this further?

November 17, 2011 11:07 AM
 

Abimov said:

I've seen this issue too, on some of the development machines and on some of the servers. We've yet to find out what makes one installation/server accept UNC paths (no validation error), and the others not.

If you experience this error, it might be solved by having full access to all levels below the given URL (\\a\b\c\d\file.txt, you will need access to folder a, b,c, and d). Try it out if possible.

August 15, 2012 8:31 AM
 

John C said:

I was curious to get more feedback on the 2nd bug on this issue which Jamie filed (588412) so I created the following bug: https://connect.microsoft.com/SQLServer/feedback/details/775415/ssis-unc-paths-network-configuration-issues-and-workarounds

December 28, 2012 5:53 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement