How to set the flat file column delimiter to an unprintable character

Question

I need to create a CSV file with a column delimiter of CTRL-A. Is that possible with the flat file destination? If it is, what's the syntax? If it isn't, is there a solution short of a custom destination?

Answer 1

I took a similar approach to sorrell, but my outcome was slightly different:

1. In SSMS, Create the character and copy the output. Note that this will look like nothing if you paste it anywhere.

   SELECT char(1)
   

EDIT:

Make sure to copy the result of this query from the results window. You can confirm you have it by pasting into notepad - it will show the cursor move one space. or in notepad++, it will show a highlighted "SOH"

Here is where I found which decimal to use: http://www.unix-manuals.com/refs/misc/ascii-table.html

2. Paste that value into the Column Delimiter of the flat file manager:

This is what the output looks like in Notepad:

More interestingly, this is what it looks like in Notepad++ (matching the Start Of Heading text - SOH from the ASCII table I posted a link to above:

Answer 2

I haven't fully tested this, but it seems doable.

1. Create a template flat file with the headings. I used Linqpad to create the Ctrl-A character using a unicode string (\u0001). You could also get there the ascii route using \x01 (same character, just pointing this out if you need to use it in code). Here's what's in my flat file.

   ColumnA□ColumnB

2. Create a Flat File Destination, and create a New Flat File Connection. Select Delimited as the type.

3. Browse to your flat file template, check the Unicode box (if unicode), and if the data should contain headers, check that box too.
4. Copy the Ctrl-A character from your template and paste it into the Column delimiter box. Then click the Refresh button.

You should now be able to work with the delimited columns. If you need to manually recreate that character in code, you can always use \x01 or \u0001.