Searching for a string in the filename and moving, renaming it using SSIS

I had to create a project ot navigate through a folder and look for a file that has  EXE string in the file name ( not as the extension) of the file. e.g. NormanEXE1234.txt. Then once the file was found it had to be copied to the archive folder and the origianl file ahd to be reamed as hcfsnorman.txt.

After almost two days of trying to figure out how to do it, I have completed the task successfully. There were not much write up on this on the internet to my suprise. I could find tutorials only on coppying and moving of the file but not searching for a particular string to match in the filename. Finally, I thought I would put this steps together and here it is....

The following screen( on the left of the screen) shows the different variables declaration. The control flow has two file system task One for archiving the files and the other for renaming the original file.Both the tasks are encapsulated in a For each loop container as we will be navigating through all the files in the folder to find the match for the files we want to move to the archive folder.
The variables userd:

• vArchiveFile : Stores the path where the file will be moved to archive ( destination folder)
• vFileName : Name of the file ( source fie name)
• vFileSourcePath :  The path of the original file where it is found
• vFilsSpec : The string to search in the filename
• vWorkingFileName : The name of the file to be renamed

Double click on the For each loop container and fill up as follows. The filespec property of  the expression of the container should be the filename since we will be searching for the filenames that has EXE string in it.

Double click the FileSystem Task editor and change the destination path variable, the source connection and the operation. The source connection called ExtendedServices is a flat file connection.

The flat file connection is created as follows where in file name we have  file://sanftps/FTPUsers/354norman/ToHCFS/*EXE*.txt

Next, the most important part of the project is to define building the expression for the connection string of the file connection to look for a certain string ( EXE - in this case) in the source folder.
Click on the (..) next to the expression in the proeprty page of the connection manager.
Select the property Conenction string and then click on the expression to open up a screen as shown in the picture below.

This is where we specify the expression for the connection string.

FINDSTRING( @[User::vFileSourcePath] + @[User::vFileName] , @[User::vFileSpec] ,1)  > 0? @[User::vFileSourcePath] + @[User::vFileName] :""

Basically, we are using FINDSTRING string function and tying it to the Boolean Operand to build the connection string. That's all.