Search code examples
sql-serverssisssis-2012sql-server-2017

SSIS Script Component - get raw row data in data flow

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.

Solution

  • 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