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