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.
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.
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.