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.
Demo
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
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:
- Fetch the file name (without the extension)
- Append the date to this string
- 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
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
Further Reading
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
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
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?
@Nakul,
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.
LikeLike
Pingback: #0421 – SQL Server – SSIS – Wild card search to find if a file exists in a folder or directory | SQLTwins by Nakul Vachhrajani
Pingback: #0422 – SQL Server – SSIS – Delete or rename files and perform other file operations without a script or writing code | SQLTwins by Nakul Vachhrajani
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
ADDITIONAL INFORMATION:
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.
(Microsoft.DataTransformationServices.Controls)
BUTTONS:
OK
LikeLike
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”?
LikeLike
its not working for me..giving “the given path’s format is not supported” error.
LikeLike
Rajat, can you please cross-check the expressions again? Are they evaluating to a valid path (the Expression evaluator as seen in my screenshots will help)?
LikeLike
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.
LikeLike