Just recently after an a production database upgrade to a 64-bit SQL Server 2008 R2 RTM (from SQL 2000), the same SSIS 2008 package that exports data from SQL Server 2000 to a flat file (csv/txt) started to wrap all data fields with _x0022_ hexadecimal characters.
The CSV file that was extracted is not totally worthless especially if it’s already taken hours to produce that file. We can easily have a quick workaround to fix this until the next scheduled export. By using Notepad/Notepad2 or other text editor, you want to replace the _x0022_ character with a double quotes (“).
Next, check your Flat File Connection Manager. Look into the Connection Managers and open the Flat File Connection Manager Editor and check the Text qualifier box. Change it to double quotes (“) from _x0022_. Click OK, save and rebuild your package.
Looking further into this, it turns out the cause of the issue is the version of SQL Server in development is different than the one in production as shown in the two pictures below.
Development (R2 SP1)
Production (R2)
In this case, DEV is more up-to-date than PROD. After upgrading the production SQL Server to SP1, the issue was resolved.
Further Reading:
SQL Server 2008 SP1 CU 6 includes small changes to .dtsx files
SQL Server 2008 R2 SP1 Download
