SSIS Expression to append file extension

Question

I'll get a Full filepath(FilePath and FileName) from a variable (@[User::V_FullPath]) as C:/Users/ABCD/Documents/Development/SampleFile.txt

I have a file with the same name but with .xlsx (SampleFile.xlsx) in another folder(A) that I want to copy to another folder(B)

To get just the filename I'm using the expression:

SUBSTRING(@[User::V_FullPath],37,47)

How can I append .xlsx to the above expression My goal is to get SampleFile.xlsx

Hadi · Accepted Answer · 2022-02-10T20:42:52.620

1

Why not just replace .txt with .xlsx?

REPLACE( @[User::V_FullPath]),".txt",".xlsx")

This will result in the following value:

C:/Users/ABCD/Documents/Development/SampleFile.xlsx

If you need only the filename Sample.xlsx, you can use TOKEN and TOKENCOUNT functions as follows:

TOKEN(TOKEN(@[User::V_FullPath],"/", TOKENCOUNT(@[User::V_FullPath],"/")), ".", 1) +".xlsx"

Expression result:

Sample.xlsx

1 Answers1