Conditional derived column expression

Question

Conditional derived column expression

Libby

Ten Centuries

Points: 1175
More actions
September 13, 2011 at 3:07 pm

#142921

I have a Data Flow Tranformation step in my package that contains several derived columns. The source of my input is a flat file and output is a SQL table. I'm using the derived columns to reformat several dates from the YYYYMMDD format to YYYY-MM-DD. The issue I have is sometimes there is nothing in the date field and causes me an error. Until I can figure out how to format the condition inside the expression, I went into "Configure Error Output" and selected "Ignore Failure" on Error. I really don't think it's a good idea to keep this, so I intend to change it back to fail.
Can someone please help me with the format of the conditional code? Among other things, this is what I've tried most recently. I added "LEN(DOB) > 0 ? " to the front of the expression, but it's not happy with it.
LEN(DOB) > 0 ? (DT_DATE)(SUBSTRING(DOB,1,4) + "-" + SUBSTRING(DOB,5,2) + "-" + SUBSTRING(DOB,7,2))
I have hunted and hunted for other posts with this issue, but can't find any. Thanks for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Place your condition in the data flow arrow leading up to your derived columns. If column > 0 then one path, else send it a different path.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Libby Ten Centuries Points: 1175 More actions · Answer 2

Thanks for your response, but I'm not sure I'm following you. Are you talking about the "Data Flow Path Editor" between the flat file the derived column transformation step? If so, I don't see where to add what you're suggesting.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

I think this is what Jason was suggesting:

http://msdn.microsoft.com/en-us/library/ms140153.aspx

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

One other note. In the expression you add LEN(DOB) > 0 ?

This creates a functional IIF which requires a True output and a false output separated by a colon

So it is

Conditional Test ? True Condition: False condition

You have only one output that I can see, you need a second output.

Libby Ten Centuries Points: 1175 More actions · Answer 5

Yes thank you, I realized later that I need the false condition. I've been trying different things, but still getting an error when the field is blank on the flat file source. Below are some things I've tried. It's almost like it's ignoring my IF and trying to perform the substring on spaces. I'm obviously not getting something...

Tried this...

(LEN(DOB)) > 0 ? (DT_DATE)(SUBSTRING(DOB,1,4) + "-" + SUBSTRING(DOB,5,2) + "-" + SUBSTRING(DOB,7,2)) : NULL(DT_DATE)

Have also tried this...

(DOB) != " " ? (DT_DATE)(SUBSTRING(DOB,1,4) + "-" + SUBSTRING(DOB,5,2) + "-" + SUBSTRING(DOB,7,2)) : NULL(DT_DATE)

Libby Ten Centuries Points: 1175 More actions · Answer 6

I think I finally got something to work. I would like to understand better why this works, but when spaces are True on my IF, then it handles the blank DOB field. I put the NULL(DT_DATE) condition before the parsing (ELSE). Prior to this change, the NULL(DT_DATE) was my ELSE.

(DOB) == " " ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(DOB,1,4) + "-" + SUBSTRING(DOB,5,2) + "-" + SUBSTRING(DOB,7,2))

If someone could explain to me why this way works, maybe the light bulb will come on for me. 🙂

Thanks so much for the help you provided.