SSIS exporting data to flat file renders double quotes as hexadecimal characters

Question

I have a simple SSIS job that exports data from the database to a flat file. I am using double quotes as text delimiters. When I run the job on my development machine everything exports fine. However, when I move the job to my staging server running Windows 2008 with SQL Server 2008 R2 the double quotes in the CSV file get exported as X0022 (hexadecimal).

Does anyone know why that is and how to fix it?

Solution 1

This looks like something we ran into a while back. Jamie Thomson documented it in a blog post - SQL Server 2008 SP1 CU 6 includes small changes to .dtsx files.

Check the version of SQL Server in both environments:

SELECT @@VERSION

Solution 2

Whilst Tom's answer is correct it does not solve the problem if you can't make changes to any of the environments, ideally you want to make a change to the SSIS package so that it runs in all environments. How about this as a work around:

Add the following expression to the TextQualifier of the Flat File connection manager:

"\""

This will generate the required double quote text qualifier, however it is stored in the package xml as an expression which avoids the issue.