Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 870
Thomas Avatar asked Feb 22 '23 13:02

Thomas


2 Answers

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
like image 94
Tom Hunter Avatar answered Apr 30 '23 06:04

Tom Hunter


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.

like image 34
Dave Sexton Avatar answered Apr 30 '23 07:04

Dave Sexton