Post reply

Way to handle conditional statments

  • September 14, 2006 at 12:39 pm

    #170357

    Hi,

    I am new to this, so bare with me....

    I have created a package to pull data from our Oracle database into our new ERP database.  How do I handle a situation where for a specific column, if the value coming from Oracle is Y, then insert 1 into the new ERP table and if the value is P, then insert 2.  I know this seems pretty simple, but where do I set that up in SSIS?  My current project Data Flow Task currently includes the OLE DB - Oracle where it is reading the tables from Oracle, a Data Conversion Task for my varchar to nchar columns, a Derived Column Task for those columns that have static data not coming from Oracle.  Do I use a Conditional Split Task to say if this column has Y, put in 1 using a seperate OLE DB Destination Task?, or create a column in the Derived Column Task with some sort of logic there?

    Thanks,

    Isabelle

    Thanks!
    Bea Isabelle

  • September 15, 2006 at 11:13 am

    #660793

    I would use the derived column.  You can use nested Conditional operators («boolean_expression» ? «when_true» : «when_false») to populate the values you need.  For example:

    (oraColumn == "Y" ? 1 : (oraColumn == "P" ? 2 : 0))

    You'll need to decide what to use if the oraColumn is not Y or P.  I used 0 in my example here.  Or, if there are more values possible for the Oracle column, you can keep nesting.

  • September 19, 2006 at 8:24 am

    #661278

    Brian has nailed this, however there is one extra consideration which may help avoid hair-pulling-out if (a) you do decide to use a Conditional Split Task and (b) you are likely to get NULLs in "oraColumn"...

    As given, the statement could return a NULL if oraColumn is NULL and the conditional split will spit - it cannot have a NULL result when resolving the expression.

    If you contain the whole of Brian's statement further within another conditional statement

    !ISNULL(oraColumn) ? (oraColumn == "Y" ? 1 : (oraColumn == "P" ? 2 : 0)) : 0

    this will return a zero on NULL or oraColumn != Y or P.

    I think this is the correct syntax but we've just moved offices and the only thing I have access to is IE - no dev tools at all - so I can't check it.  I fink it's ok.

    Matt

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

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