I am asked to export data from excel to flat file using ssis package. I have columns in excel file that is in mm/dd/yyyy date format but I want to convert it into yyyymmdd in the flat file. I have tried all measures that I know of but I am not to do it.
Can someone help me ?
Thanks
Use derived column transformation and tweak below expression as per your requirement.
(DT_WSTR,4)YEAR(Date) + “-” + RIGHT(“0″ + (DT_WSTR,2)MONTH(Date),2) + “-” + RIGHT(“0″ + (DT_WSTR,2)DAY(Date),2)
To know more about how to use derived column, follow this article.
Converting Strings to Dates in the Derived Column Component
More about Expressions
if your input columns is string, you can use this expression:
((DT_WSTR,4)Year(((DT_DATE)InputColumn)))+
RIGHT("0"+((DT_WSTR,2)Month(((DT_DATE)InputColumn))),2)+
RIGHT("0"+((DT_WSTR,2)Day(((DT_DATE)InputColumn))),2)
but if your input column is date you can use this one:
((DT_WSTR,4)Year(InputColumn))+
RIGHT("0"+((DT_WSTR,2)Month(InputColumn)),2)+
RIGHT("0"+((DT_WSTR,2)Day(InputColumn)),2)
Select the cells in question and format them:
Custom > YYYYMMDD
and then SaveAs .csv
