Friday, August 3, 2012

Text qualifier results in text file containing "_x003C_none_x003E"

There is a bug in SSIS when exporting data from SQL to a comma delimited text file where you specified no text qualifier.
This bug apparently only occurs when you develop the SSIS on a x64 win7 PC and copy the .dtsx file (windows explorer copy/paste) to network path of a x86 SQL server and schedule the job to run from SQL Agent on the same x86 SQL server.
When the SSIS runs, the text file is written out containing text qualifier = "“_x003C_none_x003E".
If you look at " _x003C_none_x003E", it actually means <none>.  x003C = "<" and x003E = ">".
If you go into the SSIS package, double-click in the connection manager section to open the flat file connection manager editor and try to clear the text qualifier removing the <none>, the <none> value get added back in.
The only work-around is to NOT open the flat file connection manager editor, but instead make the change using the property window and clear out any value in the TextQualifier field.
Other similar problems occur when you actually want to put a real value in the text qualifier.  For explain if you select the double-quote as the text qualifier and copy to a x86 server and run, you end up with a text file containing the value " _x0022" around each field instead of a double quote.