x

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
more ▼

asked Jun 08, 2011 at 08:04 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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() method of the System.IO.File 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. :-)

[3]: http://msdn.microsoft.com/en-us/library/system.io.path.aspx
more ▼

answered Jun 08, 2011 at 10:44 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jun 08, 2011 at 10:19 AM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 19 21 29

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 08, 2011 at 12:09 PM

myfirstrodeo gravatar image

myfirstrodeo
53 1 1 3

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.
Jun 08, 2011 at 12:13 PM myfirstrodeo
@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.
Jun 08, 2011 at 12:19 PM Oleg
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.
Jun 08, 2011 at 12:19 PM Pavel Pawlowski
The configuration of File System Task is a pain nearly always.. :-)
Jun 08, 2011 at 12:20 PM Pavel Pawlowski
The underscore means begin italic text so if you need to display the underscore then escape it: type \_ to see _
Jun 08, 2011 at 12:23 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927
x6
x1

asked: Jun 08, 2011 at 08:04 AM

Seen: 7988 times

Last Updated: Jun 03 at 01:58 PM