asked
SSIS Move and Rename Files
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
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). For moving file you can use the [`Move()`][1] method of the [`System.IO.File`][2] class. For managing path, file names etc you can use the methods of [`System.IO.Path`][3] class. Using the Script Task you will handle your requirements much easily. :-) [1]:
http://msdn.microsoft.com/en-us/library/system.io.file.move.aspx [2]:
http://msdn.microsoft.com/en-us/library/system.io.file.aspx [3]:
http://msdn.microsoft.com/en-us/library/system.io.path.aspx
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
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.
You need to double the backslashes. Or if you mark the text as a code, you do not need to double the backslashes inside the code section.
The configuration of File System Task is a pain nearly always.. :-)
Sorry, this is the first time I have answered a question. i didn't know it dropped all of my backslashes within paths. Kinda makes my answer harder to understand.
I have a SSIS package I am working... It is 2012 and I am using a foreach loop container with a script task, file system task inside that takes file from directory and moves them to another, once this is done I have another foreach loop container that reads the flat file and inserts into a SQL table... I have both of these processes working independently but when I try to accomplish this within one package, the second foreach loop never gets executed. Any suggestions or direction to accomplish this would be greatly appreciated. Thank You
