#0395 – SQL Server – SSIS – Adding date and time to a file name after processing

As we have been discussing over the last couple of weeks, moving data from a “source” to a “destination” is the crux of SSIS. The data source can be literally anything – a SQL Server  database, a web-service, a script, an XML or even a conventional delimited file.

Picking up files from a folder and processing them is one of the most common use-case that I have seen. In order to avoid processing the same file over and over again, the most common requirement is to append the file name with a date-time value. In this post, I will provide an expression I use to achieve this goal.

Appending a Date/Time and renaming a file in SSIS

The easiest way to append a date/time value to a file name and renaming the file in SSIS is via SSIS expressions.

Put every simply – an expression is a formula, a combination of variables, literals and operators that work together and evaluate to a single value.

We can use the following expression to yield a date value:

SUBSTRING(@[User::FileName], 1, FINDSTRING(@[User::FileName],".",1) -1 ) + (DT_WSTR,4)YEAR(GETDATE()) + (DT_WSTR,2)MONTH(GETDATE()) + (DT_WSTR,2)DAY(GETDATE())

This can then be appended to the file name and a simple File System Task can rename the file for us on the file system. Let’s see this in action with an example.


Assume a scenario where I have a set of files in a folder, and I need to do some processing on them. After processing, I need to update the file names with the date. For the sake of brevity of this example, I will not be performing any other operation on the files other than renaming them.

My folder containing the input files looks like this:

Folder with input files which need to be renamed once processing is complete

Folder with input files which need to be renamed once processing is complete

Now, I create an SSIS package that contains a simple For Each file iterator and a File System task.

For Each Loop configuration

The For Each Loop (configured as a file iterator) uses an expression populated by a variable to get the source directory information.

The file name and extension is fetched into a variable which will be used during the processing and subsequent renaming.


Overall package configuration showing the For Each File Iterator


For Each Iterator – Collection configuration using a variabel for the source directory


Fetching the individual file name & extension into a variable

Now, I create a new user variable “OutputFileName” and use the expression below to generate the output file name. The expression has essentially 3 distinct parts:

  1. Fetch the file name (without the extension)
  2. Append the date to this string
  3. Fetch the file extension and append to the modified/new file name
@[User::SourceFolder] + 
SUBSTRING(@[User::FileName], 1, FINDSTRING(@[User::FileName],".",1) -1 ) + (DT_WSTR,4)YEAR(GETDATE()) + (DT_WSTR,2)MONTH(GETDATE()) + (DT_WSTR,2)DAY(GETDATE()) + 
SUBSTRING(@[User::FileName], FINDSTRING(@[User::FileName],".",1), LEN(@[User::FileName]))
Output File Path expression

Output File Path expression

This variable is now used in the configuration of the file system task which is responsible for renaming the file.

File System Task configuration

The file system task is an extremely flexible task component in SSIS. It can operate on the file system not only by using file connections but also on the basis of variables! For our problem, we will leverage this flexibility of the File System task.

As can be seen from the screenshot below, my File System task has been configured as follows:

  • Source File Path is a variable
  • Destination File Path is a variable
  • File Operation type = “Rename File”

The “Rename File” operation renames the file specified by the old file path and renames it to the name specified by the new file path.

File System Task configuration using “Rename File” mode where input & output file names are supplied via variables

Once the package is executed, we can see that the files in the source folder are now updated as expected.

Source Folder with the File Names updated as expected

Source Folder with the File Names updated as expected

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.


9 thoughts on “#0395 – SQL Server – SSIS – Adding date and time to a file name after processing

  1. Pingback: #0396 – SQL Server – SSIS – Move and rename a file – “Could not find part of the path.” File System Task error | SQLTwins by Nakul Vachhrajani

  2. Jeff Moden

    Don’t you just love it when someone slams a post with low marks but doesn’t have the nads to say why they gave it the low mark?

    I can’t judge this article because I don’t use SSIS but, from the looks of it, it’s well written and takes the audience through things step-by-step. Keep writing and let’s hope that if someone pounds a low mark on an article, they’ll take the time to provide some insight as to why so folks can learn even more.


  3. Pingback: #0421 – SQL Server – SSIS – Wild card search to find if a file exists in a folder or directory | SQLTwins by Nakul Vachhrajani

  4. Pingback: #0422 – SQL Server – SSIS – Delete or rename files and perform other file operations without a script or writing code | SQLTwins by Nakul Vachhrajani

  5. Ben

    Thanks for providing this detailed guide, i’m running into a problem where I add the script and it doesn’t like the -1 after the first substring.

    I’m creating a Name Property expression and then copying your script. when i evaluate the expression, i get the following error:

    TITLE: Expression Builder

    Expression cannot be evaluated.

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%C2%AE%20Visual%20Studio%C2%AE&ProdVer=15.9.28307.1342&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476


    The length -1 is not valid for function “SUBSTRING”. The length parameter cannot be negative. Change the length parameter to zero or a positive value.

    Evaluating function “SUBSTRING” failed with error code 0xC004708B.





    1. nakulvachhrajani Post author

      Hello, Ben! Thanks for trying out the script. SUBSTRING will return the error when the length becomes negative. The only reason for this would be that the file name does not have the dot (.) character.
      Can you check if your “For Each Loop” has been configured to return “Name and Extension”?


  6. wilksyworld

    This was a brilliant post, and you managed to help me do exactly what i wanted to do but I can explain the errors everyone is experiencing, it simply come down to that you didn’t explain about setting up the variables, you can just about see it from your screen shot where you set up the “OutputFileName” but not quite.

    Firstly “FileName” and “SourceFolder” need to be given an Initial value, you used “Sample.txt” and “E:\Temp\RenamingFiles\FileDump\” respectively but it could be anything, these need to be set before you can create “OutputFileName” as otherwise the code can’t be evaluated as its evaluated against NULL hence the -1 issue. Also “SourceFilePath” you mention is a variable but haven’t mentioned that it is an expression of simply @[User::SourceFolder] + @[User::FileName] once I realised this the whole thing sprung into life for me.

    Thank you again as I will use this technique a lot now and understand much more about using variables in SSIS.



Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.