2

I am processing a flat file in SSIS and one of the requirements is that if a given row contains an incorrect number of delimiters, fail the row but continue processing the file.

My plan is to load the rows into a single column in SQL server, but during the load, I’d like to test each row during the data flow to see if it has the right number of delimiters, and add a derived column value to store the result of that comparison.

I’m thinking I could do that with a script task component, but I’m wondering if anyone has done that before and what would be the best method? If a script task component would be the way to go, how do I access the raw row with its delimiters inside the script task?

SOLUTION:

I ended up going with a modified version of Holder's answer as I found that TOKENCOUNT() will not count null values per this SO answer. When two delimiters are not separated by a value, it will result in an incorrect count (at least for my purposes).

I used the following expression instead:

LEN(EntireRow) - LEN(REPLACE(EntireRow, "|", ""))

This results in the correct count of delimiters in the row, regardless of whether there's a value in a given field or not.

hyphen
  • 2,368
  • 5
  • 28
  • 59

1 Answers1

2

My suggestion is to use Derrived Column to do your test

And then add a Conditional Split to decide if you want to insert the rows or not.

Something like this:

Dataflow

Use the TokenCount function in the Derrived Column box to get number of columns like this: TOKENCOUNT(EntireRow,"|")

Derrived column

holder
  • 585
  • 2
  • 9
  • I like the conditional split idea, so I’ll probably use that, but I’m more wondering how I can do the test part. How can I access the raw row with its delimiters and test it within the context of the data flow? – hyphen Jun 18 '19 at 10:30
  • Which column delimiter is used in the file? – holder Jun 18 '19 at 10:32
  • It’s a pipe delimiter – hyphen Jun 18 '19 at 10:33
  • 1
    If you map the entire row to one column you can use the TOKENCOUNT function in the Derrived Column to get the number of delimiters – holder Jun 18 '19 at 10:44
  • 1
    I ended up going with a slightly different take on your answer (See edit), but still giving you the check. thanks! – hyphen Jun 18 '19 at 12:45