SSIS - Convert Multiple Column Values To Null
I am using SSIS (SQL Server 2008 R2) to transform an input CSV file into an SQL table. Five columns in the input file (reals - e.g. 19.54271) occasionally have a bad value (strings - e.g. "NAN") that cause the package to fail.
What is the simplest way to check these 5 columns for the bad value "NAN", convert that into either a NULL value or known bad numeric (-9999), and write the corrected values into the same final SQL table?
I have the following mess going so far, and finally decided to ask if there is a simpler way...
My current conditional logic:
My Case1 Derived Column Conversion:
Note: Still not sure if I can combine the other derived columns into one instance, but since my destination can have only one input, I suspect I will need to...
SSIS expressions get hairy and hard to read when the logic is complex or if there are multiple evaluations. In your case you're going to wind up with a bunch of tasks that, individually, do very little.
I'd bundle this up into a script component. That way you could use basic vb or c# functions to evaulate if all of your columns properly convert to numeric and assign defaults when they don't. Additionally, you can implement a try/catch scenario and gracefully send errors to a different output buffer.
Here's some examples of how to use the script component as a transformation:
It looks like using a script component would be the best way to proceed if my logic had been more complex than simply converting bad values into nulls.
However, the logic with transformation objects is fairly straightforward, so hopefully this can help someone else:
The package (note that I redirect rows for suspect columns in the datasource):
The conditional split logic: [EDIT: I found that every case condition requires a separate processing path. If you are evaluating multiple expressions, you can do so in one case by appending them with the || operator.]
The derived column logic: