Loading data from a pipe delimited file in SSIS

Question

Loading data from a pipe delimited file in SSIS

brad.luettke

Mr or Mrs. 500

Points: 515
More actions
May 19, 2010 at 6:19 am

#139083

Hello,
I am looking to move a database from 2000 to 2005, and one of the associated tasks the database has is to run a DTS package that takes a pipe delimited file produced by our iSeries and loads it into sql. When trying to build an SSIS package to do this, I am unable to get it to recognise when the columns will be. There is a vertial pipe column delimiter option, but it is unable to interpret this. Below is a snippet of the file that needs to be loaded:
351000¦40000938¦31638271¦
It appears to work in 2000 and DTS, but I can't see why or what setting it sees to know that the pipes are column delimiters.
Has anyone else come across this before?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

As I see it, the symbol ¦ is not listed as one of the delimiters in the dropdown menu of the flat file configuration manager.

You have 3 possible solutions:

* let the source system generate files with a delimiter known by SSIS, such as |

* use a script task to replace the delimiter ¦ with another delimiter. Then use the flat file source to read the file.

* read the file with a script component

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

It is available as the "Vertical Bar" Column delimiter in the Columns panel of the Flat File Connection Manager.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Ed-86789 (5/19/2010)
It is available as the "Vertical Bar" Column delimiter in the Columns panel of the Flat File Connection Manager.

Nope, that is the | symbol, while the original file has the symbol ¦ as a delimiter.

It is a difference, I tested it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

I never knew there was a difference. I don't see anything like that in the standard ascii character table. If you look at it in a hex editor, what is the code?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

| --> %7C hex and | unicode

¦ --> %A6 hex and ¦ unicode

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

Ah... a special unicode character. Well that does make it a bit messy then. We also have an iSeries system, but I have never had to deal with a situation like this one. Thanks for the clarification - although I hope this a little piece of trivia I never have to put to use.

brad.luettke Mr or Mrs. 500 Points: 515 More actions · Answer 7

Hello,

Thanks for the above. When loading up character map and finding the U+00A6 Broken Bar, when selecting it then copying it from there, then pasting it over the top of the Column delimiter options and refreshing in the Flat File Connection Manager Editor, it all works ok.

So thanks for the help.

Cheers