Problem with Row Delimeter on Flat File Connection Manager

Question

Problem with Row Delimeter on Flat File Connection Manager

Brian Carlson

SSCarpal Tunnel

Points: 4170
More actions
August 17, 2011 at 8:54 am

#255795

I am reading a flat file that has additional rows I want to ignore. The additional rows are status rows and occur rarely and have no column delimeters.
Example:
Col1Data,Col2Data,...
Server stopping
Server starting
Col1Data,Col2Data,...
I have set the Row delimiter to "{CR}{LF}" and the Column delimiter to "Comma {,}". SSIS is prefixing the status rows onto the first column of the next row. ("Server stopping[CRLF]Server starting[CRLF]Col1Data") It seems that the column delimiter is taking precedence over the row delimiter. How can I change this behavior?
Thanks,
Brian.

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

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

Phil Parkin SSC Guru Points: 246978 More actions · Answer 1

Brian Carlson (8/17/2011)
I am reading a flat file that has additional rows I want to ignore. The additional rows are status rows and occur rarely and have no column delimeters.
Example:
Col1Data,Col2Data,...
Server stopping
Server starting
Col1Data,Col2Data,...
I have set the Row delimiter to "{CR}{LF}" and the Column delimiter to "Comma {,}". SSIS is prefixing the status rows onto the first column of the next row. ("Server stopping[CRLF]Server starting[CRLF]Col1Data") It seems that the column delimiter is taking precedence over the row delimiter. How can I change this behavior?
Thanks,
Brian.

I'm afraid you're in trouble, this probably is not as easy as you would wish.

A non-elegant, yet fairly simple, way of doing it is to create a dataflow task which reads the records as one field per row (ie ignoring column delimiters) and uses a conditional split to filter out the records you don't want and then writes the records back out to a flat file which you can import using your existing routine.

Alternatively, read in the records one row at a time (as above), filter out the garbage (also as above) and then carve the data into fields within a script component. More elegant as it requires only one pass of the file, but more involved too.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 2

Thanks for the feedback. I thought I was missing something simple. What I did was to increase the length of the first input column, then parsed it in a derived column. It's less change to the existing package that way.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 3

I should have paid more attention - I didn't notice that you had only two columns. You've found a good solution - fingers crossed you don't ever get any more columns to try to parse out in a derived column transformation:-)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 4

Actually, it's 30 columns. I didn't put them all there.

I found a slightly better solution. The first column is DB server but it can be derived from File Server so I can ignore the input column entirely. If there is ever a column in the status columns, it will probably mess the data up though. That's not very likely though because this is a system generated file. I've only ever seen three status messages: Server Starting, Database Starting, and Database Stopping.

Thanks,

Brian.