I have a source file which has a Data column as ddMonyyyy(10Mar2013), I need to convert this during my transformation, I can populate this into a staging and then do a convert on the column but I want to do it through SSIS, is there a way? Is using Script task the only way, if yes how can this be done.
You can use a [derived column transformation] then reformat your date with the expression: datepart("year", yourcolumn) + datepart("month", yourcolumn) + etc... **-- Added based on follow up --** Since your source column is in a string format that SSIS cannot directly cast as a `DT_DATE` you'll need your derived column expression to massage the string prior to the cast by inserting some hyphens between the parts of the date: (DT_DATE)(SUBSTRING(datestring,1,2) + "-" + SUBSTRING(datestring,3,3) + "-" + SUBSTRING(datestring,6,4)) This assumes that you always have a three character month. Adjust as necessary :) After you do this, you can use the datepart approach outlined above (or just leave it as a date and it can be manipulated any number of ways once you have it in the database). :