CODEWITHSUNDEEP
error-handlingssisdelimiterscript-component
kahless364
kahless364

Reputation: 35

Detecting partial rows in SSIS

Situation: A tab delimited row from a flat file source is missing columns at the end of the row. The rows are delimited by {CR}{LF} in the Flat File Connection Manager, and the last column is delimited by {CR}{LF} as well. All other columns are delimited by Tab {t}. SSIS is importing the row.

Example:

Column_1{t} Column_2{t} Column_3{t} Column_4{CR}{LF}

123{t} 123{t} 123{t} 123{CR}{LF}

123{t} 123{CR}{LF}

123{t} 123{t} 123{{t} 123{CR}{LF}

123{t} 123{t} {t} {CR}{LF}

123{t} 123{t} 123{t} 123{CR}{LF}

Problem: A partial row that does not have the remaining columns tab delimited (see row 2 above) treats the following row as a part of the current row, while a row containing the tab delimited columns that are blank (see row 4 above) does not.

Desired Output: An error is desired to signal a partial row.

What is the best method to check for partial rows in the middle of a file?

Upvotes: 0

Views: 1338

Answers (1)

kahless364
kahless364

Reputation: 35

It appears that pre-denali (2012) SSIS fails if the missing column when parsing. This is fixed in 2012 by always checking for the row delimiter.

See: http://blogs.msdn.com/b/mattm/archive/2011/07/17/flat-file-source-changes-in-denali.aspx

Work arounds for this issue in pre-2012 SSIS include writing your own parser (this is what we chose to do), converting the data before parsing it, or using the Flat File Source just to parse rows.

Upvotes: 1

Related Questions