none
how to assign null in derived column transformation editor RRS feed

  • Question

  • Dear friends, can any one tell me  how to assign null to the expression value in derived column transfromation editor?

    thanks,

    Wednesday, March 28, 2007 7:23 AM

Answers

  • In the editor dialog, expand the Null Functions folder, and drag the appropriate NULL(your data type here) item to the expression box. For example, you'd use NULL(DT_I4) to create a null integer.
    Wednesday, March 28, 2007 1:25 PM
    Moderator

All replies

  • In the editor dialog, expand the Null Functions folder, and drag the appropriate NULL(your data type here) item to the expression box. For example, you'd use NULL(DT_I4) to create a null integer.
    Wednesday, March 28, 2007 1:25 PM
    Moderator
  • I find the ISNULL function (when used in SSIS... Works fine in T-SQL) will not allow a null return value in a standard ISNULL expression..

     

    example: ISNULL(ColumnName) ? expression1:expression2

    Where expression1 is the value to return if the item is null and expression2 is the value to return if the value is not null

     

    I have tried to use NULL(whatever) in expression 1 but the syntax is rejected.  However, I CAN use other expressions, such as "", etc.    Also, changing the datatype of the output using the advanced editor does not work either.  Have tried changing the datatype to all available types, but no dice..

     

    Here is an example:

     

    ISNULL(MyDate) ? NULL(DT_DBDATE) : Mydate

    Any suggestions?

     

     

    Thursday, April 22, 2010 4:19 PM
  • Your expression should work. Is it possible that the field [MyDate] is of type DT_DATE, not DB_DATE ?

    You can't say 'If A then <something> of Type 1, ESLE <something else> of Type 2'

    SSIS is VERY Type intolerant. They have to match exactly.


    Todd C MSCTS SQL Server 2005
    Thursday, April 22, 2010 4:57 PM
  • Hey, you put me on the right track, Thanks a million

     

    I tried to cast the expressions on both sides of : but for some reason just would not work for date (DT_DBDATE was what I was going for...).  Tried every conceivable data type and variation on the expressions, but no luck.  

     

    However, I opted to use a string and let SSIS place the value in the target (a SQL Server datetime column) and viola' - worked.

     

    Basically the solution was ISNULL(column) ? NULL(DT_STR,10) : (string expression)

     

    (In my case, this was a long substring expression to simply add dashes to a plain 8 character date YYYYMMDD and add dashes to make it load easily into the SQL Server datetime columns. ) 

    I am sure we have been through these types of values thousands of times, but for some reason, this one gave us trouble.

     

    -J Oliphant, Data Architect

     

     

    Thursday, April 22, 2010 5:51 PM