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

SQL Server 2008 SP1 CU 6 includes small changes to .dtsx files

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:

image

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

Published Thursday, November 25, 2010 11:21 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

 

Valentino Vranken said:

Thanks for sharing that Jamie, very useful!

I encountered this problem in the following situation.  We're currently upgrading to R2 and decided to start with one server (the test/UAT server).  This is our SSIS/SSAS backend server.  Since the upgrade, your flat file imports started failing.

Indeed, when opening the packages in BIDS R2, I saw the _x0022_ appearing as Text qualifier instead of the usual double-quote.  Replacing it with " fixed the issue.

Best regards,

Valentino.

March 16, 2011 9:21 AM
 

Koen Verbeeck said:

Hey Valentino,

so you fixed the issue by manually editing the .dtsx file?

Won't the issue come back when you edit the package and redeploy it?

Koen

May 9, 2011 8:19 AM
 

spe109 said:

Does anybody know if there is a fix for this in SQL 2008 R2. I don't really fancy having to go through and edit all the packages.

October 11, 2011 6:57 AM
 

jamiet said:

Hi spe109,

The "fix" is to make sure everyone is running the same service pack and cumulative update.

regards

Jamie

October 11, 2011 7:27 AM
 

spe109 said:

Thanks Jamie. The problem is more a migration problem. Packages have been developed in R1 and are getting moved to R2 which is the standard for new installations, with R1 installations planned for upgrade. It just seems odd to have released fixes for R1 and not for R2.

October 13, 2011 3:27 AM
 

DrWho said:

So if i had a server upgraded to 08 from 05 and i use VS 05 to edit the ssis packages i need be using 08? also i noticed a weird Square character in the configurations area of job properties, that fires off the ssis. wnet in and looked at command line and it should have been a space, deleted added space and it was fine.

October 19, 2011 8:41 AM
 

suhani said:

hi

this is suhani

i am providing some good information

          1803 when working on SQL Server 2012. The script I ran against a SQL Server 2012 instance was

CREATE DATABASE [suspect_db] ON  PRIMARY

( NAME = N'suspect_db', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'suspect_db_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

thanks

<a href="http://21stproperty.com/">Property Search</a>

November 4, 2011 4:13 AM
 

SSIS Junkie said:

I have come across a bug in Visual Studio 2010 Database Projects ( aka datadude aka DPro aka Visual Studio

November 21, 2011 4:51 AM
 

kashish said:

Guys,

I am facing issue that my ssis is processing flat file successfully in visual studio 2005 and when i deploy it locally also and run the package through batch file it is running successfully

but when we deploy the package on a QA server it fails and log shows error "Delimiter not found "

the column delimiter is comma and row delimiter is cr lf

Please help anyone

the same file when i open in excel and resave it as csv it processes successfully in qa also.

Problem is file has "" for every column when it comes to us but when i resave it by auto formatting in excel , it looses "" and auto format it .

i cannot change the file .the problem is similar files have been processing successfully in past .

On local SSIS version is basic and on qa its SP2

November 27, 2012 10:12 AM
 

MeeLood said:

Hi,

Thanks for this great article, we're facing this kind of problem on UAT environment, everything worked fine in DEV and Integration.

I wanted to know if we must check the version of sql server engine or somtehing else ? is the version of dtexec.exe the same as the engine ?

I mean the version of sql engine in DEV and INT is 10.50.2500.0 (sql server 2008 R2 SP1) and in UAT too (version given by query: select @@verion) so it should work or am i missing something ?

Thanks

December 12, 2012 8:26 AM
 

jamiet said:

Hello MeeLood,

I would expect the version number of the SQL Server database engine to change based on the Cumulative Update (CU). Certainly the aforementioned article http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/ indicates that that is the case.

JT

December 12, 2012 8:44 AM
 

MeeLood said:

Thanks for your reply Jamie. After investigation it seems ssis packages are stored on different server than the one holding the instance of the database server. And indeed the version of dtexec in UAT is 10.50.1600.1 whereas the package was designed with a 10.50.2500.0 version of sql server.

Manually editing the dtsx to replace _x0022_ by " did the trick.

December 28, 2012 9:22 AM
 

Yates said:

We had the same problem whilst developing on 2008 R2 SP2 and pushing to production on 2008 R2 (RTM). Resolved it by manually editing the packages.

We are currently testing SP2 before it gets rolled out to production which is why there is a difference.

February 15, 2013 5:50 AM
 

McKinney said:

Jamie, thank you so much for this blog post.  I had been fighting a problem like this for probably 16 hours, and mostly I was assuming I was too stupid to do my job.  I should have recognized that "it worked on the client" but "not on the server" and thought immediately of version incompatibilities, but since I auto-update my workstation, it didn't occur to me that optional updates might be the solution.  I found them, and my problems immediately went away.

THANK YOU THANK YOU THANK YOU.

March 12, 2013 1:45 PM
 

jamiet said:

Hi Mckinney,

You're very welcome, I'm delighted that it helped someone!

March 12, 2013 1:51 PM
 

Rich said:

This helped a lot, thank you!

June 4, 2013 7:21 AM
 

Gareth said:

Cheers for this. Was tearing my hair out.

This may not be proper procedure but it worked.

Went onto the SQL Server and opened the DTSX file.

Checked the Text Qualifier and sure enough it was set as _x0022_.

So I changed it to " and ran the SQL job.

And it works perfectly now - so I'm not touching it again!

Thanks again,

Gareth  

June 13, 2013 11:37 AM
 

Paula said:

I can't tell you how much this helped me.  I had the same issues....and kept thinking it must be permissions.  

It is working perfectly now.  Thanks soooo much.

April 14, 2014 2:05 PM
 

Will said:

http://support.microsoft.com/kb/977889

The following are the character ranges for low-order non-printable ASCII characters that are rejected by MSXML versions 3.0 and later:

#x0 - #x8 (ASCII 0 - 8)

#xB - #xC (ASCII 11 - 12)

#xE - #x1F (ASCII 14 - 31)

Is the double quotes in that range?

August 26, 2014 4:13 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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