Derived column expression validation error

1
votes

I have write the below code in my derived column expression in ssis package.

Input values as i am receiving from source:-

Q:\SOURCE\RV_T\PCL_RVT\RVT_export_all_2011-02-14_08.38.00_Emerald.xlsx

i want :- RVT_export_all_2011-02-14_08.38.00_Emerald.xlsx

Substring(@[User::V_FilePath],LEN(@[User::V_FilePath]) - Charindex("\",Reverse(@[User::V_FilePath]))+2,LEN(@[User::V_FilePath]))

its running fine in SSMS but giving below error in SSIS

Error at DFT - Load Data [Derived Column [8]]: Attempt to parse the expression "Substring(@[User::V_FilePath],LEN(@[User::V_FilePath]) - Charindex("\",Reverse(@[User::V_FilePath]))+2,LEN(@[User::V_FilePath]))" failed. The token """ at line number "1", character number "68" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Error at DFT - Load Data [Derived Column [8]]: Cannot parse the expression "Substring(@[User::V_FilePath],LEN(@[User::V_FilePath]) - Charindex("\",Reverse(@[User::V_FilePath]))+2,LEN(@[User::V_FilePath]))". The expression was not valid, or there is an out-of-memory error.

Error at DFT - Load Data [Derived Column [8]]: The expression "Substring(@[User::V_FilePath],LEN(@[User::V_FilePath]) - Charindex("\",Reverse(@[User::V_FilePath]))+2,LEN(@[User::V_FilePath]))" on "Derived Column.Outputs[Derived Column Output].Columns[filenaemfrompat]" is not valid.

Error at DFT - Load Data [Derived Column [8]]: Failed to set property "Expression" on "Derived Column.Outputs[Derived Column Output].Columns[filenaemfrompat]".

Please suggest. Thanks in advance...

1
ssisssis-2012

1 Answers

1
votes

Finally after lot of testing i am able to get what i want.

Here is the code i used

RIGHT(@[User::V_FilePath],FINDSTRING(REVERSE(@[User::V_FilePath]),"\\",1) - 1)