none
SSIS convert DT_WSTR to DT_Date

    Question

  • Hi ,

    I am working On SSIS 2008,
    I am Loading data from Excel source to Sql table .
    One of my Column is "LeavingDate".
    Example Values in Column are Below

    NULL
    NULL
    NULL
    8-Dec-11
    9-Dec-11
    10-Dec-11

    From Excel Source the DataType is DT_WSTR,but my Destination Sql table is "Date" dataType .
    I tried usind DATACONVERSION AND DERIVED COLUMN To insert data into my Sql table ,but every time  it failed.
    I tried using DT_Date and DT_DBdate in Derived Column  to convert my Leavingdate into date format but it failed .


    NOTE:My 90% of the "Leavingdate" column values are and will be null.
    is it because of null value I am not able to achieve my task ??or

    if some one could provide any Expression to be use in derived column transformation

    Any Help?



    • Edited by PriyaKhan Friday, January 20, 2012 4:36 PM
    Friday, January 20, 2012 4:35 PM

Answers

  • I see what is going on.

    Well, your date example you need to convert the date to the proper format, right now you are trying to get the year from position 7 to 4 whereas you do not even have the full year.

    Also, DT_DBdate is not a SSIS expression, must be DT_DATE

    NULL(DT_DATE) will not give you the null, use the DBNULL variable.

    You need to convert the three letter months to numbers

     (MONTH(RowDate) == 1 ? "Jan" : MONTH(RowDate) == 2 ? "Feb" : MONTH(RowDate) == 3 ? "Mar" : 
      MONTH(RowDate) == 4 ? "Apr" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "Jun" : 
      MONTH(RowDate) == 7 ? "Jul" : MONTH(RowDate) == 8 ? "Aug" : MONTH(RowDate) == 9 ? "Sep" : 
      MONTH(RowDate) == 10 ? "Oct" : MONTH(RowDate) == 11 ? "Nov" : MONTH(RowDate) == 12 ? "Dec" : 
      "ERR") + " (" + (DT_WSTR,4)YEAR(RowDate) + ")"

    Arthur My Blog

    Friday, January 20, 2012 6:49 PM

All replies

  • two SSIS expression need to be in one Derived Column transformation:

    1) Check for NULL and then ?? How to treat the NULLs? Do you need to insert a blank or what?

    2) 10-Dec-11 is perhaps not the date that your backend supports, or it is?


    Arthur My Blog
    Friday, January 20, 2012 4:42 PM
  • Please have a read through Converting Strings to Dates in the Derived Column Component.
    Todd McDermid's Blog Talk to me now on
    Friday, January 20, 2012 4:45 PM
  • ArthurZ,

    Thanks for the reply,

    yeah I need to insert Null in my Destination Sql Table  where ever I get Null in my Source Excel "Leavingdate"Column .
    And where it is not null I want to insert date Example 8-Dec-11 in my Sql table

    Note :My Sql table "Leavingdate" Column is of Date  datatype

    I tried using below :


    (ISNULL([ LeavingDate ])) ? NULL(DT_DBdate) : (DT_DBdate)(SUBSTRING([LeavingDate], 7, 4) + "-" SUBSTRING([LeavingDate], 1, 2) + "-" + SUBSTRING([LeavingDate], 4, 2))

    But Derived column is not accepting This expression :( and comming in Red Font

     


    • Edited by PriyaKhan Friday, January 20, 2012 4:58 PM
    Friday, January 20, 2012 4:45 PM
  • I see what is going on.

    Well, your date example you need to convert the date to the proper format, right now you are trying to get the year from position 7 to 4 whereas you do not even have the full year.

    Also, DT_DBdate is not a SSIS expression, must be DT_DATE

    NULL(DT_DATE) will not give you the null, use the DBNULL variable.

    You need to convert the three letter months to numbers

     (MONTH(RowDate) == 1 ? "Jan" : MONTH(RowDate) == 2 ? "Feb" : MONTH(RowDate) == 3 ? "Mar" : 
      MONTH(RowDate) == 4 ? "Apr" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "Jun" : 
      MONTH(RowDate) == 7 ? "Jul" : MONTH(RowDate) == 8 ? "Aug" : MONTH(RowDate) == 9 ? "Sep" : 
      MONTH(RowDate) == 10 ? "Oct" : MONTH(RowDate) == 11 ? "Nov" : MONTH(RowDate) == 12 ? "Dec" : 
      "ERR") + " (" + (DT_WSTR,4)YEAR(RowDate) + ")"

    Arthur My Blog

    Friday, January 20, 2012 6:49 PM