Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Header Row Delimiter Ignored in SSIS Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 8:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 1,419, Visits: 1,585
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!
Post #1468582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse