CODEWITHSUNDEEP
sqlsql-serverssis
Raymond A
Raymond A

Reputation: 793

SSIS 2012 - Redirect Rows with Special Characters

Im using SSIS 2012 and im importing data from a flat file source -> Temp table -> Final table. Any errors found are redirected to an error File.

However, im tio apply a new constraint that redirecting all rows of a specific column containing special charaters into a separate error file prior inserting it into the temp table.

Im trying to use a conditional split but it creates a case for each specific charaters ,using the Findstring() function. What are the other options available?

Upvotes: 0

Views: 2468

Answers (2)

Matt Gibson
Matt Gibson

Reputation: 38238

How many specific characters are you going to be dealing with? If it's only a few, you can combine FINDSTRING()s together with the || "or" operator in the Conditional Split, e.g.:

(FINDSTRING( [test_column] , "A", 1) > 0) || (FINDSTRING( [test_column] , "B", 1) > 0) || (FINDSTRING( [test_column] , "C", 1) > 0)

would filter rows with test_columns containing "A", "B" or "C" to a single split.

If that's too unwieldy because of the number of characters, then a Script Component, as billinkc suggests is the way to go. (It's especially appropriate if the special characters can be represented as a regualar expression, as script components have access to very good regex functions.)

Upvotes: 1

billinkc
billinkc

Reputation: 61221

I'd look at adding a Script Component into your data flow and have it validate your data.

Add a column called HasJunkData to the output of type boolean (DT_BOOL). If you have multiple things to validate, depending on the level of granularity required, you might want a flag per input column or simply decide at the row level whether its good or bad.

Inside the script itself, I'd use something like How to recognize if a string contains unicode chars? to identify the presence of bad characters or potentially a regular expression depending on what "special characters" means to you.

Upvotes: 2

Related Questions