I don't know what to write in Derived column expression.

Steps- 1.Source - Excel file 2.Derived column 3.Destination - OLE DB Destination

1 Answer 1


If you have the date in a string value, and know it is truly MM/DD/YY not M/D/YY (i.e. will have zero padding as needed so 2022-01-01 will be sent as 01/01/22 not 1/1/22), and you want a string value out, then you just need to use the SUBSTRING function to pull out the right characters to form a new string. If you might get years before 2000 then you'll need a conditional to decide is you are concatenating 19 or 20 to make YYYY from YY. There are a number of answers here and on StackOverflow and articles elsewhere covering that in sufficient detail.

If you want to generate an actual date value (a value of a type like DT_DATE or DT_DBTIMESTAMP) from such a string, just use a Data Conversion task to convert to data, and make sure that the locale is set correctly (open the advanced properties for the conversion step and you'll find it under component properties / common properties, called LocaleID, set it to "English (United States)" for MM/DD/[YY]YY).

  • I'm gonna try this.. thanks @David Nov 9, 2021 at 15:43
  • I didn't understand how to write it can anyone help? Nov 16, 2021 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.