I am working in SSIS. I am splitting a pipe-delimited, quote-qualified flat file into separate flat files. The flat file is ragged, i.e., the number of columns varies because of different record types. I have 3 record types -- A, B, and C. A has 2 columns, B has 3 columns, and C has 4 columns. The flat file connection manager has only 1 column defined [Column 0] (in which I read the entire row as 1 column) to allow for column counting. I have a conditional split after the flat file source that determines if each record type has the correct number of columns. Those records with deficient / surplus columns get written to an error flat file. My condition for each record type is pretty simple:
LEN([Column 0]) - LEN( REPLACE([Column 0], "|", "") ) != x
where x is the given record type's column count minus 1. (For example, since C has 4 columns, I expect C to have 3 pipe characters as column delimiters. So, for C, x = 3.)
The problem I'm running into is that this does not account for the file being quote-qualified, i.e., I am erroneously replacing pipe characters that are part of each column's string (as long as that string is wrapped by quotation marks).
Is there a way to avoid counting the quote-qualified pipe characters?
