ssis - How to read a flatfile with lowercase thorn as the delimiter

Question

I'm trying to read a flat file in SSIS which is in this format

col1 t col2 t col 3

I'm using the flatfile connection manager but there is no option for the 't' character in the column delimiter section of the connection manager.

What would be the workaround for this? Other than reading the file and replacing the thorn character with a SSIS supported delimiter,

Answer 1

Being a dumb 'merican, I think the lower case thorn character is 0xFE while upper case is 0xDE. This will become important soon.

I created an SSIS package with a Flat File Connection Manager. I pointed it at a comma delimited file that looked like

col 1,col 2,col 3

This allowed me to get the metadata set for the file. Once I have all the columns defined and my package is otherwise good. Save it. Commit it to your version control system. If you're not using version control, shame on you, but then make a copy of your .dtsx file and put it somewhere handy.

Replace the comma delimited file with the a thorn delimited one.

What we're doing

What we're going to do is edit the XML that is our SSIS package by hand to exchange the delimter of a , with a t. It's a straight forward operation but since you are going off the reservation, it's easy to foul up and then your package won't open up properly in the editor.

How to fix it

If you have the package open, close the package but leave Visual Studio open. Right click on the file and select "View Code".

In an SSIS 2012 package, you'll be looking for

DTS:ColumnDelimiter="_x002C_"

In a 2008 package,

<DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x002C_</DTS:Property>

What we're going to do is substitute _x00FE_ (thorn) for _x002C_ (comma). Save the file and then double click to open it back up.

Your connection manager should now show the thorn symbol on the Columns tab.

Interestingly enough, after you open the package, if you go back into the Code, the editor will have swapped the thorn character into the file in place of the hexagonal character code. Weird.