question

Mrs_Fatherjack avatar image
Mrs_Fatherjack 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
ssisforeach-loopmove-rename
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

myfirstrodeo avatar image
myfirstrodeo answered
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.
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@myfirstrodeo The backslash is an escape character so you have to escape it (type two of them in a row to get one displayed). I will edit your answer in a minute.
1 Like 1 ·
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.
1 Like 1 ·
The configuration of File System Task is a pain nearly always.. :-)
1 Like 1 ·
The underscore means begin italic text so if you need to display the underscore then escape it: type \\_ to see \_
1 Like 1 ·
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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.