Header Row Delimiter Ignored in SSIS

• brdudley

  SSCrazy

  Points: 2320

  More actions

  June 28, 2013 at 8:54 am

  #276783

  I'm dealing with a file where the header row is different that the rows in the body, in that it does not end in a delimiter. This is probably a bug and will be resolved, but it uncovered a behavior that seems to imply that the Header Row delimiter parameter is being ignored. I've tested with 2008 R2 and 2012, both show a problem, but they are not consistent with each other.

  Consider a file:

  One|Two|Three

  1|1|1|

  2|2|2|

  3|3|3|

  Set up a Flat file connection as a delimited file with Column names in first row, Header Row delimiter is {CR}{LF}, Column Delimiter is {|}, and Row Delimiter is {CR}{LF}.

  In 2008 R2, the last column will include the pipe, i.e. 1|, 2|. In 2012 it does not!

  My guess was to change the Row Delimiter to |{CR}{LF}. Both 2008 R2 and 2012 now treat the first two rows as header and add more columns, but they do not process the rows consistently. 2012 gives 2 rows with data in the first 3 columns, but 2008 R2 gives a single row.

  Similarly, when writing files, it seems like the Header Row delimiter is not used.

  Can anyone confirm or explain this behavior? Is this a bug or by design.

  If I really need a difference, I can code around it. Mostly want to understand the behavior.

  Thanks!