At work earlier this week an SSIS package in our environment was failing inexplicably and after a great deal of effort my superhuman colleague Tom Hunter discovered the root cause of the problem and I thought it might be worth sharing, particularly as a poster to the SSIS forum earlier today encountered the same problem.
The problem was in regard to a dataflow that contained a Flat File Source Adapter. The dataflow had been working fine but was now failing for seemingly no reason at all. Tom discovered that the cause was the following line inside the .dtsx file that formed part of the definition of the Flat File Connection Manager, more accurately its the property that defines how textual values get delimited:
<DTS:Property DTS:Name="TextQualifier" xml:space="preserve">_x0022_</DTS:Property>
We compared that to older versions of the package in our source control system and discovered in those that the property was defined as follows:
<DTS:Property DTS:Name="TextQualifier" xml:space="preserve">”</DTS:Property>
I have highlighted the differences. Nobody had changed the TextQualifier property, not deliberately anyway, so why had it changed and what was this strange _x0022_ value that had turned up? The answer to the last bit is actually quite simple; 0022 is the hexadecimal representation for (decimal) 34 and 34 just happens to be ASCII code for “ as you can see from this small snippet I have taken from www.asciitable.com:
OK so the value of _x0022_ made more sense but again, why had it been changed? It turns out that there was a bugfix in SQL Server 2008 SP1 Cumulative Update 6 that changed the way in which some property values get stored in the package (it affects the ColumnDelimiter property too). The fix was described as
FIX: Error message when you try to run an SSIS 2008 package that contains a Flat File source and a text qualifier uses some special characters that are not supported by the W3C XML language specification: "An invalid character was found in text content"
and is described in more detail in Knowledge Base article 977889.
So they changed the way certain property values get stored, that’s fine. But still we were puzzled as to why this caused us a problem. The root cause (as most of you may have probably already worked out) was that the machine on which the package was last edited had had Cumulative Update 6 applied to it yet the machine where the package was failing had not hence the big ugly red failures. The solution was to get all of our machines up to the same build version.
There is of course a simple but important lesson here – make sure your developer workstations are running the same builds as your DEV, Test, UAT and Production boxes. Its the sort of problem that you should only encounter once because you’ll never make this stupid mistake twice. Right?
If you need to investigate this problem yourself and want to know which cumulative update you have applied then Steve Jones of SQLServerCentral fame has a great article at SQL Server 2008 Build List (login required) that provides all of the build numbers for each SQL Server 2008 service pack and cumulative update and also tells you how to go about discovering your particular build number.
Hope this helps. Don’t make the same mistake that we did!
@Jamiet