Monday, June 14, 2010

How to stop quotation marks qualifying specific columns in a text export from SQL Server

In an attempt to create a text file from a database in SQL using an SSIS package to replace the same file's creation from Access, I ran into a snag. That snag was that the Access file was not qualifying certain fields with quotation marks, but SQL was qualifying every field with quotes. The program that used the text file was created by a third party so I didn't wan't to have them change the programming to accommodate this change, and the text qualifying was causing problems. Instead I turned to Google and found that controlling this was a lot easier than I thought it would be. Here's how to specify which columns should not be qualified by quotation marks, assuming you're doing so through the use of an SSIS package.

1. Open your SSIS package responsible for exporting to the text file
2. In the "Connection Managers" section at the bottom, right-click on the connection for your text file and choose Edit
3. You'll open up the settings on the General page by default. Change to the Advanced page
4. Select the column you do not want to have qualified by quotation marks
5. In the window to the right, set TextQualified to False
6. Repeat 4 and 5 for all other columns you do not want qualified

This also stopped qualifying the header row for me though. I needed all header names to still be qualified with quotes, so to get around it I renamed those particular headers to contain quotes in their actual name. You can do that right in the same Advanced section as step #3 above. Once you rename your headers though, you'll also have to go into the actual text file destination object under the Control Flow to make sure those headers get remapped. Adding the quotation marks will prevent those columns to be recognized for auto-mapping. Once you've done that go ahead and give your new package a try and you should see that the text qualifiers are shut off for the fields you set TextQualifed to false.

No comments: