SSIS convert DT_WSTR to DT_Date
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?
January 20th, 2012 11:52am

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
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2012 11:59am

Please have a read through Converting Strings to Dates in the Derived Column Component. Talk to me now on
January 20th, 2012 12:02pm

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
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2012 12:02pm

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
January 20th, 2012 2:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics