CODEWITHSUNDEEP
sql-serversql-server-2008ssis
MTAdmin
MTAdmin

Reputation: 1033

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...

enter image description here

My current conditional logic:
enter image description here

My Case1 Derived Column Conversion:
enter image description here

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...

TIA

Upvotes: 1

Views: 1650

Answers (2)

MTAdmin
MTAdmin

Reputation: 1033

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):
SSIS package

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.] Case Condition Logic

The derived column logic: derived column logic

Upvotes: 1

brian
brian

Reputation: 3695

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:

http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/

http://www.sqlis.com/sqlis/post/The-Script-Component-as-a-Transformation.aspx

http://www.codeproject.com/Articles/193855/An-indespensible-SSIS-transformation-component-Scr

Upvotes: 2

Related Questions