Post reply

SSIS Derived Column?

  • November 30, 2012 at 11:15 am

    #285424

    New to SSIS. Using BIDS 2008R2, but ultimately targeting SS2k12....

    I'm working through an ETS project. Import CSV as string columns (even the apparent numbers...), Discard some rows, Derive a column which is defined like...

    IF UnitCost is zero or null,

    ActualUnitCost=AverageCost

    ELSE

    ActualUnitCost=UnitCost

    ENDIF

    Can't quite figure out how to accomplish this derived column.

    Jim

  • November 30, 2012 at 11:26 am

    #1564510

    You will be using an Expressin in SSIS which is similar to c#

    however the If is implied which is confusing

    Quick Link

    http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx

    Your expression will look something like

    ISNULL( UnitCost ) || UnitCost == 0? AverageCost : UnitCost

    Edit: Added suggested expression

  • November 30, 2012 at 11:50 am

    #1564512

    Perfect, I'll go try that. One more question, if you don't mind...

    I have some nulls (",,") in one or more numeric columns in the .tsv input file. Are those going to come across as nulls? (I bring them in as strings and then convert them to numeric after they're in....)

    Thanks again

    Jim

  • November 30, 2012 at 2:29 pm

    #1564577

    in Derived Column you can

    use this expression to check NULL values

    ISNULL( [ColumnName]) ? 0 : [ColumnName] -- if it is an Interger

    ISNULL( [ColumnName]) ? "Some Value" : [ColumnName] -- if it is an String

Viewing 4 posts - 1 through 3 (of 3 total)

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