Flat File Connect Mgr not well behaved

Question

Flat File Connect Mgr not well behaved

mountcrumpit

Say Hey Kid

Points: 663
More actions
March 17, 2010 at 8:41 pm

#212541

Sample Illustration flat file source data:
MSH|33451|^%~|1234|asdf^24324^^NY|<cr>
EVN|A08|abde|4332|popeye^bluto^OliveOyl||20100317|1400|<cr>
where <cr> is a 0d carriage return. (I line feeded for readability)
and | is the column delimter, no text qualifier.
Note that the first record (beginning with MSH) has only 5 columns, The second record has 8.
If I were to import this in 2000EM via the Connection:Text File Source, it will find the maximum
columns. and therefore have no trouble when it reads the entire file, nulling the destination columns for any source columns not existing (or empty).
in 2005 Flat File Connection it seems only the first record is read -- so the columns after 5 end up wrapping into the next records _and_ the <cr> seems to be ignored (ie its embedded in column contents) and ditto with the | delimiter.
In the real world file in question's first rec has 12 (active) columns, but some records have up to 54.
When its brought in the first rec looks almost ok. but the other (longer recs) have their column 12 stuffed with the remainder of the record contents something like [abd|123||||a^b^|||||||] ?!
I thought maybe code page? no doesn't seem to make a difference. In 2000EM I used file type ANSI -- but i don't see that anywhere -- Is that gone?
Does anyone know what's up? is this a service pack reward? It'd be nice if there was "Ragged - delimited" as a type that might work.
Please someone, throw me a bone. I have screen caps i can post if this isn't clear enough.
thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

In SSIS 2005, the Flat File source is quite static with delimeters, meaning the numbers of columns are fixed. (otherwise SSIS can't map the metadata to corresponding columns in the dataflow). So if you say there are 12 columns, he will read 12 columsn, stuffing possible extra columns in the last one.

A possible solution is to modify your file first with a script task, making sure that all rows have an equal amount of columns. Or just read the file with scripting, that's possible too...

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

andresito Ten Centuries Points: 1032 More actions · Answer 2

I would import the text file as a single-column, ragged right flat file source. Then, use the "Derived Columns" transformation to parse out the row and create your columns. Then, you'll have all your columns in the data flow. I don't know your file well enough to know if you can count on certain columns being in certain positions (e.g. behind the Nth "|" delimiter), but that would present some problems with any method you're using, I'm assuming.

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 3

I would suggest using a script task because it helps you split them as you need. Even derived Column will do but using script task will give you more flexibility..

Its your call

mountcrumpit Say Hey Kid Points: 663 More actions · Answer 4

thanks folks for all your help,

I'm going the scripting route as you suggest. I'm most familiar with that and need some productivity gain compared to the last few days.

I found it humorous, that in my research on this issue and reading hundreds of forum posts, that one MVP type said that SSIS was a replacement not an upgrade -- in response to someone asking/complaining about backwards/forwards compatibility.

Ok, SSIS may be a replacment -- but the package suffix (.DTSX) implies upgrade (DTS -> DTSX) Or maybe it was MicroSlop humor: SSIS is an eX-DTS:hehe:

thanks again.

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

mountcrumpit (3/18/2010)
Ok, SSIS may be a replacment -- but the package suffix (.DTSX) implies upgrade (DTS -> DTSX) Or maybe it was MicroSlop humor: SSIS is an eX-DTS:hehe:

That's because it was decided very late in the development cycle that Microsof would change the name from DTS to SSIS, so it was too late to change every reference of DTS in the code. Have a look around in the XML files of a SSIS project, you will find DTS everywhere 🙂

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