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
asked Jun 08, 2011 at 08:04 AM in Default
The File System Task is not so comfortable, and sometimes hard to configure properly.
You can try to put a Script Task into the ForEach loop container, pass variables with source and destination file name into it and script whatever you want in VB.NET (SQL Server 2005 and above) or C# (SQL Server 2008 and above).
Using the Script Task you will handle your requirements much easily. :-): http://msdn.microsoft.com/en-us/library/system.io.path.aspx
answered Jun 08, 2011 at 10:44 AM
I'm not an SSIS person but came across this on SSC forum, appears they are trying to do the same thing or similar to what you are:http://www.sqlservercentral.com/Forums/Topic560772-148-1.aspx
answered Jun 08, 2011 at 10:19 AM
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
Next I created 5 more String variables named
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.
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
Highlight vDest_FileVar and open properties.
Highlight vSource_FileVar and open properties.
Now drop your File System Task into your Foreach Loop Container. Destination Connection will be
This should work with some modification for your needs, although it isn't quite as pretty as using a Script task to handle it.