Problem while trying to assign NULL value to a datatime column using Dervied column

  • Hi All,

    in the flat file that we receive , sometimes we receive the value for datetime column as '00000000' (CCYYMMDD format) . If i get date value as 00000000 , then i should make that column value as null and insert into the table. Else , i will insert into that date value into that table.

    i read the column as string(from the flat file), and assign into variable PROC_DT.

    then i use the derived column transformation like this :

    PROC_DT == "00000000" ? (DT_DBTIMESTAMP)NULL : (DT_DBTIMESTAMP)(SUBSTRING(PROC_DT,5,2) + "/" + SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))

    But getting error message. Can any one help me in handling this?

    Thanks,

    Arunvijay

  • You can't cast a NULL value as a specific datatype. It's either NULL or it's not.

    Secondly, you can't mix strings and datetime values in SSIS. So either pick a fake date for the NULL value or Derive your other datetimes as a string and convert them later. (MS uses 01/01/1900 for their default fake dates)

    Lastly, for this to work properly, you'll want to make sure that your final destination column accepts NULL values.

    PROC_DT == "00000000" ? "NULL" : (SUBSTRING(PROC_DT,5,2) + "/"

    + SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))

    After this, you can use a T-SQL Convert or Cast to change the value to Datetime where it's not NULL. But SSIS won't allow mixing of datatypes in a Derived Column Task.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie is right that you can't mix data types. I'd do this:

    PROC_DT == "00000000" ? NULL(DT_STR, 8) : (SUBSTRING(PROC_DT,5,2) + "/"

    + SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))

    The NULL(DT_STR) produces a null value of the correct data type, which will translate to SYSTEM.DBNULL.VALUE so a null gets inserted into the database.

  • Jack,

    That is a SWEET workaround for NULL. I didn't even know about it and I just happen to need it.

    Thanks!

    EDIT: And you know, as soon as I posted this, I went to try it out and it errored out on me.

    "For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations".

    Darnit. Nice try, though.

    SECOND EDIT: WHOOPS. It has to be DT_WSTR, not DT_STR... WSTR works. @=) Thanks again.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks all for ur replies.

    I used the below transformation :

    PROC_DT == "00000000" ? "NULL" : (SUBSTRING(PROC_DT,5,2) + "/" + SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))

    This had no syntax errors , but the loading failed . The reason being , the database column is of "datetime" datatype , but the output of the derived column is "DT_STR" datatype.

    How can i resolve this?

    Thanks,

    Arunvijay

  • As I said in my previous post, you're going to have do another convert.

    You can try to do it all in one task, but you might need two Derived column tasks.

    First, change the "NULL" TO NULL(DT_WSTR,10) so that the Conversion doesn't bomb out on the literal string "NULL".

    Then see if this works:

    (DT_DBTIMESTAMP)(PROC_DT == "00000000" ? NULL(DT_WSTR,10) :

    (SUBSTRING(PROC_DT,5,2) + "/" + SUBSTRING(PROC_DT,7,2) + "/" +

    SUBSTRING(PROC_DT,1,4)) )

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • try this

    (PROC_DT == "00000000") ? NULL(DT_DATE) : (DT_DATE)((SUBSTRING(PROC_DT,5,2) + "-" + SUBSTRING(PROC_DT,7,2) + "-" + SUBSTRING(PROC_DT,1,4)))

    Regards
    Venkat
    http://sqlblogging.blogspot.com[/url]

  • Thanks Folks. The solution works gr8

  • Hi,

    I am having a similar problem, except when I imported the rows, it will check to see if the value is null and if it is, then it will assign it a predetermined value.. like this.. the other part in there checks if it's out of range and uses a different value for that..

    If Not rowValues(1).IsNullOrEmpty(rowValues.GetValue(1).ToString) Then

    Row.CholesterolResultDate = DateTime.Parse(rowValues(1).ToString())

    If DateTime.Parse(rowValues(1).ToString()) = CType("12/31/9999 11:59:59 PM", DateTime) Then

    Row.CholesterolResultDate = CType("1/1/1900 12:00:00 AM", Date)

    End If

    Else

    Row.CholesterolResultDate = DateTime.Parse("01/01/1901")

    so if I were going to use a derived column, then it would need to check for that specific date ( 01/01/1900 ) . Any suggestions for getting it to goto dbnull.value on the wrote to the db..

    write now it's using a transform script to write to the db,, and in there it checks this for the date.. if it's 1901 then it will assign the parameter like this:

    oCmd.Parameters.Add("@BloodPressureDate", SqlDbType.DateTime).Value = DBNull.Value

    but I am trying to remove that validation to earlier in the project so that I can run a faster write script to the db.. maybe a oledb destination with the fast load option..

  • ok,, I came up with this as a possible for the derived column expression..

    YEAR(CholesterolResultDate) == 1901 ? NULL(DT_DATE) : CholesterolResultDate

    I haven't checked it running yet, but I thought I'd post that..

  • Thanks!...This helped me!

  • It's always good to see the "legacy" effects of old threads. @=)

    Glad it helped.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 12 posts - 1 through 11 (of 11 total)

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