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.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  August 17, 2011 at 9:09 am

  #1369669

  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.

  ---

• Brian Carlson

  SSCarpal Tunnel

  Points: 4170

  More actions

  August 17, 2011 at 9:42 am

  #1369697

  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: 247158

  More actions

  August 17, 2011 at 10:09 am

  #1369719

  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:-)

  ---

• Brian Carlson

  SSCarpal Tunnel

  Points: 4170

  More actions

  August 17, 2011 at 10:22 am

  #1369726

  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.