derived column 1

[SQLScholar]

Hey all,

I have a data import which has a field which contains dates or NULL. It comes from a CSV file. I am trying to replace all empty values with null with a derived column.

This is what i have

LEN( [EXPIRY_DATE] )==0 ? NULL(DT_STR) : [EXPIRY_DATE]

Obviously being a CSV all data is coming in as text.

But i am getting this error.

The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Any ideas?

Dan

 

[ousoonerjoe]

Assuming you mean for this to be in SQL code:

CASE WHEN LEN([EXPIRY_DATE]) = 0 THEN NULL ELSE [EXPIRY_DATE] END

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[SQLScholar]

Errr.... nope. I need to do a derived column in SSIS

Like defined here.

http://sqlblog.com/blogs/andy_leona...ge-and-the-derived-column-transformation.aspx

Or you can conditionally replace the MiddleName value. In this case, we'll replace Null middle names with a space. If not Null, we'll allow the existing value to continue in the data flow pipeline. To do this, use the ternary syntax:

[Test Condition] ? [True Operation] : [False Operation]

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[RiverGuy]

Try adding the length/codepage and casting that null declaration.

LEN([EXPIRY_DATE]) == 0 ?  (DT_STR, 50, 1252) NULL(DT_STR, 50, 1252)   : (DT_STR, 50, 1252)[EXPIRY_DATE]

 

[SQLScholar]

Sorry for taking so long to get back to you - got pulled in other direction

Yes - that worked perfectly. Thanks mate.

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------