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

avatar image

5.3k 66 69 77

(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` class.

Using the Script Task you will handle your requirements much easily. :-)

more ▼

answered Jun 08, 2011 at 10:44 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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:


more ▼

answered Jun 08, 2011 at 10:19 AM

avatar image

6.6k 21 26 34

(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

avatar image

53 2 1 5

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jun 08, 2011 at 08:04 AM

Seen: 11866 times

Last Updated: Jun 03, 2014 at 01:58 PM

Copyright 2018 Redgate Software. Privacy Policy