I am creating an SSIS package that amongst other things needs to move some files then rename them adding the date to them. I can get the move bit working using the ForEach Loop Container, and I can get a rename working for one file, but can't get a rename using the ForEach Loop. Can someone please point me to a good blog/article that can guide me through this step by step. Many thanks
You can do this using just SSIS if you don't have the programming background, (like me). I assume you are using a ForEach File Enumerator in your Foreach Loop Container and pulling all files that exist within a certain folder and moving them to another folder with the date appended to the filename. For simplicity's sake I am doing this for just .txt files, but if you are renaming and moving more than one file type, it adds a level of complexity. First, create a String variable call vDate, and set it to whatever date format you are appending to your filename. I did it using an Execute SQL Task with a Single Row Result set and statement `SELECT CONVERT(CHAR(8),GETDATE(),112)` with the result set mapped to Result Name 0, Variable Name User::vDate. Next I created 5 more String variables named `vSource_FileName, vSource_FilePath, vSource_FileVar, vDest_FilePath, vDest_FileVar`. Now create your Foreach Loop Container point it at the folder containing your Source files. Set Retrieve file name to Name Only. Variable mappings will point to User::vSource_Filename, Index 0. Set your vSource_FilePath to the same location you have pionted your Foreach Loop Container to, don't forget the backslash on the end of the directory (ie. `C:\SCRIPTS\`). Now you will set up the rest of your variables. Set vDest_FilePath to the path you are dropping your files off at without a filename set. I set mine to a subfolder of the source location `C:\SCRIPTS\DESTINATION\`. Highlight vDest_FileVar and open properties. `EvaluateAsExpression` set to true. `Expression = @[User::vDest_FilePath] + @[User::Source_FileName] +"_"+ @[User::vDate]+".TXT"` Highlight vSource_FileVar and open properties. `EvaluateAsExpression` set to True. `Expression = @[User::vSource_FilePath]+ @[User::Source_FileName]+".txt"` Now drop your File System Task into your Foreach Loop Container. Destination Connection will be `IsDestinationPathVariable = True, DestinationVariable = User::Dest_FileVar`. Source Connection will be `IsSourcePathVariable = True, SourceVariable = User::vSource_FileVar`. This should work with some modification for your needs, although it isn't quite as pretty as using a Script task to handle it.