SSIS Renaming a file with file extension appearing in the correct place

Question

I'm needing to rename the file contained within the variable @FilePath, but the problem I'm facing is the extension where I can't figure out how I can get the date before the csv file extension. How could I get around this?

This is how the variable values come out currently:

User::FilePath  {C:\\SSIS\\Files\\Template.csv} String

User::FilePathRename    {C:\\SSIS\\Files\\Template.csv_19_05_2019_14_39_07.csv} String

FilePath has the following expression:

@[User::SourceFolder] + "\\" +  @[User::FileName]

SourceFolder is the folder the ForEach loop is searching through and reading each .csv file.

FilePathRename has the following expression:

@[User::FilePath] + "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , @[User::FileDate]), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , @[User::FileDate]), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , @[User::FileDate])
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[User::FileDate]), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[User::FileDate]), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[User::FileDate]), 2)
+ ".csv"

Answer 1

SSIS expression approach

You can use TOKEN and TOKENCOUNT to achieve that, try the following expressions:

@[User::SourceFolder] + "\\" 
+  TOKEN(@[User::FileName], ".", 1) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , @[User::FileDate]), 2)
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , @[User::FileDate]), 2)
+ "_"
+ (DT_STR, 4, 1252) DATEPART("yyyy" , @[User::FileDate])
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[User::FileDate]), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[User::FileDate]), 2) 
+ "_"
+ RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[User::FileDate]), 2)
+ ".csv"

Script Task approach

But this expression may not be accurate if the file name contains dots (example: filename.1.xml), i prefer using a Script Task to achieve that using the following line of code:

DateTime FileDate = (DateTime)Dts.Variables["User::FileDate"].Value;
string oldFile = Dts.Variables["User::FilePath"].Value.ToString();
string newFile;
newFile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(oldFile),System.IO.Path.GetFileNameWithoutExtension(oldFile)) + "\\" + FileDate.ToString("_dd_MM_yyyy_HH_mm_ss") + ".csv";
Dts.Variables["User::FilePathRename"].Value = newFile;

References

• How do I extract file name from a path within an SSIS package?
• How to store 'fully qualified' and 'name only' file name in SSIS variable