Ignore bad rows - not alphanumeric

Question

I need to ignore or redirect bad rows (i.e. contain special characters)

I have tried using a conditional split component but I do not know what special characters I may receive in my data source. They vary from (but not restricted to) the following:

㔀

Ѭ

####

I know that that I can use a script component and a REGEX to match various patterns but what I need to do is the following:

Ignore rows where primary keys fields only have alphanumeric values (as my primary key values cannot contain NULL and will not parse when a special character exists)

However, my primary key fields are Date, Time and Site so I also need the REGEX pattern to allow '/' and ':' and '.' for Date and Time (with precision)

If the special character only exists in Site and not Date or Time, I can replace with "" and process the row.

Can this be done using REGEX and script component?

Answer 1

There are some very options offered here:

http://stackoverflow.com/questions/3891331/find-non-ascii-characters-in-one-varchar-column-or-mulitiple-columns-using-sql-s

Answer 2

So are you infiltering a composite Primary key or more than just the primary keys?

In general, if you do not know what to redirect, it is not doable.

I feel like you have two questions or more in one post, and if you need help with RegEx this is not the right forum to ask.

I do not see how to help you, sorry.

---

Arthur

MyBlog

Twitter

Answer 3

Hi DarrenOD,

According to your description, you want to filter out non-alphanumeric values for Date, Time and Site columns, except '/' and ':' and '.' for Date and Time columns.

To achieve your requirement, we can use Regular Expressions in Script Component. For more details, please refer to the following links:
http://www.purplefrogsystems.com/blog/2011/07/pattern-matching-in-ssis-using-regular-expressions-and-the-script-component/
http://stackoverflow.com/questions/18210181/system-text-regularexpressions-regex-replace-error-in-c-sharp-for-ssis

Thanks,
Katherine Xiong

---

Katherine Xiong
TechNet Community Support