Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS Nugget: Move and rename a file in one operation

Raul Villaronga posed a question on the SSIS forum today regarding the FileSystem task at http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=85827

The FileSystem task can move and rename files but Raul wanted to do both of those operations at the same time. As far as I can see (I stand to be corrected) this can't be done without 2 instances of the FileSystem task. There is a way around it though using our trusty script task and believe me, it couldn't be simpler.

First you need to set up 2 variables. One to hold the name and location of the file you want to move and another to hold the name and location that you want to move it to. Just like this:

20050914Variables.JPG

Then drag a script task onto the control-flow surface and add these 2 variables to the ReadOnlyVariables property:

20050914ReadOnlyVariables.JPG

And finally...the code. I've added a TRY...CATCH block and the firing of some events to flesh it about a bit because, to be quite honest, it looks a bit forlorn otherwise!!!:

20050914Code.JPG

The bit that does all the work of course is the line starting File.Move(...  Don't forget to import System.IO

OK, this is fairly noddy and I'm not pulling up any trees here am I it has to be said? But once again we've demonstrated just how easy to is to use SSIS in ways that you simply can't with competitor tools. In fact I'm beginning to think there isn't really a competitor to SSIS because its in a category all of its own. It really is so much more than an ETL tool.

-Jamie

 

Published Wednesday, September 14, 2005 9:10 PM by jamie.thomson

Comments

 

Thank you said:

Your site is realy very interesting.
February 23, 2006 12:07 AM
 

Source File Archiving: SSIS versus DataStage « Miscellaneous Ramblings on Data Warehousing said:

November 14, 2006 4:44 PM
 

john said:

I used the sample as a template to check if a file exists in a location...

Try

           Dim SourcePath As String

           Dim SourceFile As String

           Dim FileProcessedCheck As String

           Dim FileProcessingCheck As String

           FileProcessingCheck = Dts.Variables("PaymentFileSoureDirectoryFile").Value.ToString

           SourcePath = Dts.Variables("PaymentFileTargetDirectory").Value.ToString

           SourceFile = Dts.Variables("PaymentFileName").Value.ToString

           FileProcessedCheck = SourcePath & "\" & SourceFile

           If File.Exists(FileProcessingCheck.ToString) Then

               'Check to see if file is being processed

               Dts.Events.FireInformation(411, "ScriptComp", "File" & SourceFile.ToString & " is processing", "", 114, False)

               Dts.TaskResult = Dts.Results.Failure

           Else

               'Check to see if file exists in Processed folder

               If File.Exists(FileProcessedCheck.ToString) Then

                   Dts.Events.FireInformation(411, "ScriptComp", "File" & SourceFile.ToString & " has already been processed", "", 114, False)

                   Dts.TaskResult = Dts.Results.Failure

               Else

                   Dts.Events.FireInformation(411, "ScriptComp", "Executing FTP task for " & SourceFile.ToString & "", "", 114, False)

                   Dts.TaskResult = Dts.Results.Success

               End If

           End If

       Catch ex As Exception

       End Try

April 20, 2007 10:17 PM
 

Tomek said:

well,

I just used "Rename fle" option and it does all the trick. It moves and changes the name  in one task.

May 31, 2007 2:56 PM
 

Matt Blodgett said:

Tomek is right.  No reinvention of the wheel necessary.

It's awfully unintuitive that the developers of SSIS wanted us to use "rename" for this purpose rather than "move", though.

November 13, 2007 9:56 PM
 

robin said:

is it same proceure to add date extension in OLE DB Destination. I am trying to create different staging table for every day but have not been able to change file name. For eg: tblProduct20060203, tblProduct20060604

June 11, 2008 3:51 PM
 

Colin said:

I concur with Tomek,

The File System task does the trick without writing any custom code. The only issue is that you might have to check if the file actually exists before trying to archive/move it. That is probably why the code above was written.

I found a nice blog that shows how to deal with this. It can be found at the following URL:

http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

Ciao,

Colin

August 13, 2008 7:28 AM
 

david said:

and how can i change just the filename but in the same folder???

August 21, 2008 10:14 PM
 

jamie.thomson said:

David,

Use the Rename operation of the FileSystem task.

-Jamie

August 22, 2008 8:33 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems