Extract date value using SSIS derived column

Question

I have a file named Name_Excaa_2021-11-28.xlsx and I want to extract only the date value 2021-11-28 using a derived column.

I am using the expression below, but it gives me the output 2021-11-28.xlsx

RIGHT((DT_STR,50,1252)@[User::FileName],FINDSTRING(REVERSE(@[User::FileName]),"_",1) - 1)

How can I solve this and get the date value 2021-11-28

Thanks in advance.

Answer 1

Assuming that your file names always end with the format yyyy-mm-dd.xlsx you can just select that part using right to grab the last 15 characters and then extract just the 10 characters of the date from those 15 characters using left:

left(right(@[User::FileName],15),10)