Help with an SSIS expression

  • I need another set of eyes.

    I have a Derived Column Transformation set up in a package in SSIS 2008. I want to replace any nulls with a value of 0, or else pass the existing UrgencyId on through the expression.

    Here is the expression that I am using:

    ISNULL(UrgencyID) ? 0 : UrgencyID

    However, when I check results in a DataViewer on the other side of this transformation, I still have a value of null in one record. UrgencyId is a four-byte signed integer. Any ideas?

    Please let me know if I need to provide additional details.

  • Have you tried wrapping quotes around the 0? I know you said it is an integer, but I have had success with that in the past.

    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

  • Jason, thanks for the suggestion, I'll keep it in mind for future use.

    The problem was not in this expression, but in another similarly named column that still contained a null. After putting a second derived column into the transformation, the problem was resolved.

    (Well, actually the problem is that there is bad data being fed to me. These columns should never be null.)

    Sometimes the best solution is to put the problem away for the night and look at it again in the morning. 🙂

  • I agree that clear-headed, new look is always helpful!

    for future reference, I've found that

    ISNULL(field) == TRUE yields consistent results.

  • Scott Arendt (1/17/2012)


    Jason, thanks for the suggestion, I'll keep it in mind for future use.

    The problem was not in this expression, but in another similarly named column that still contained a null. After putting a second derived column into the transformation, the problem was resolved.

    (Well, actually the problem is that there is bad data being fed to me. These columns should never be null.)

    Sometimes the best solution is to put the problem away for the night and look at it again in the morning. 🙂

    Cool - glad you found it.

    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

Viewing 5 posts - 1 through 5 (of 5 total)

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