i am new to SSIS.I learning SSIS,by creating some sample packages.I am loading data from .csv file to SQL server table.In csv file i have one column as varchar format (eg:22-Feb-2012),but i need to convert into date format as YYYY-MM-DD.Can you help me in creating Expression for this conversion.
Let me know if you need further details.
If the input format is 22-Feb-2012, the below expression should work (assuming your input column name is [Col]):
(DT_WSTR, 4)DATEPART("YEAR", (DT_DATE)[Col]) + "-" +
RIGHT("0" + (DT_WSTR, 2)DATEPART("MONTH", (DT_DATE)[Col]), 2) + "-"+
RIGHT("0" + (DT_WSTR, 2)DATEPART("DAY", (DT_DATE)[Col]), 2)
Note that I've put newline characters in the expression above to make it a little easier to read. If you try to paste it into a Derived Column expression, it will only paste the first line.
Also, notice this expression casts the value to a DT_DATE 3 times. It may be more efficient to do a Data Conversion to convert your varchar column to DT_DATE, and then do the rest of the logic in a derived column. Or you could do string manipulations for the day and year (assuming a 2 digit day format, e.g. 01):
RIGHT(Col,4) + "-" +
RIGHT("0" + (DT_WSTR,2)DATEPART("MONTH",(DT_DATE)Col),2) + "-" +
SUBSTRING(Col,1,2)
