IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact
You are here: Home / Database / Removing _x0022_ Character Issue in SQL Server and SSIS

Removing _x0022_ Character Issue in SQL Server and SSIS

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.

CSV in Excel

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 (“).

CSV in Notepad2

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.

SQL Server Flat File Connection Manager Editor

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.

SQL Server 2008 R2 SP1 Development

Development (R2 SP1)

SQL Server 2008 R2 Production

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

May 22, 2012 Filed Under: Database Tagged With: ETL, SQL Server, SSIS

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Buy me a coffee Support this site
Buy Me a Coffee?

Categories

  • .NET
  • Coding
  • Cybersecurity
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Programming
  • Resources
  • Review
  • Tips and Tricks
  • Use Case
  • WordPress
  • Writing

Recent Posts

  • How to Check Installed .NET Framework Version
  • How to Remove .NET Runtime and SDK on Mac
  • How to Solve Intermittent 403 Error in IIS
  • How to Show Hidden Folders and Files in Mac Finder
  • How to Solve MS Office VBA Compile Error UserAuthentication

Recent Posts

  • How to Check Installed .NET Framework Version
  • How to Remove .NET Runtime and SDK on Mac
  • How to Solve Intermittent 403 Error in IIS
  • How to Show Hidden Folders and Files in Mac Finder
  • How to Solve MS Office VBA Compile Error UserAuthentication
  • RSS

Tags

.NET Access Amazon ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack Linux macOS Microsoft Microsoft SQL Server MVC Office PHP PowerShell Python Simple Mail Transfer Protocol Smtp Server SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Windows Windows 8 Windows 10 Windows Server

Copyright © 2011-2022 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure