SSIS Script Component - get raw row data in data flow

Question

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.

holder · Accepted Answer · 2019-06-18 10:51:55Z

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:

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

edited Jun 18, 2019 at 10:51

answered Jun 18, 2019 at 10:25

holder

5852 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hyphen Over a year ago

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?

holder Over a year ago

Which column delimiter is used in the file?

hyphen Over a year ago

It’s a pipe delimiter

holder Over a year ago

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

hyphen Over a year ago

I ended up going with a slightly different take on your answer (See edit), but still giving you the check. thanks!

Collectives™ on Stack Overflow

SSIS Script Component - get raw row data in data flow

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related