问题:

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?

回答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

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

回答3:

just if it helps, I solved this issue creating a final step with a script task where I replace in the file generated all the "x0022" strings with the right value.