Moving and Renaming a File

Question

I am new to SSIS and have just put together a facility to load data from a file.  

I would like to rename the file with a date/time in the name and move it to another folder.  How do I do this ?  I need to know:

>>> How do I obtain the file name and rename it with the date time?
>>> How do I move the file ? Use Visual Basic to copy it, then delete the original?

I have not VB.Net before so I am a little apprehensive.....

---

PG

Answer 1

You can use the File System Task to copy, move, rename, and delete files.

The requirement(s) for using that task would be that you know the original file name and the file name you want to rename/move/copy it to.  Typically, you'll be doing operations like that inside a Foreach Loop Container - iterating over the contents of a directory - so you'll have the "original" file name.  You'll have to "construct" the destination file name somehow.  Usually, this is done by using another SSIS variable, but by setting the "EvaluateAsExpression" property on it to TRUE.  Then you change the Expression property to "build" the file name (probably by using the original file name variable as a base) and adding the date or other characters.

---

Answer 2

Todd
Thanks for your reply.   I have not marked it answered just yet, until I can be sure.  But it does provide some useful information about how to proceed.

What I want do is load about about 6000 files containing 1.5 million records.   Two fields to load are in the file, one further field is in the file name.  I then have to execute several look ups to retrieve keys for selecting records, then either inserting, updating or doing nothing.

From what I can see so far, its not clear to me exactly how to operate the EvaluateAsExpression and how to pick up that value in another SSIS task or in VB .Net script.   With luck, today I will do sufficient reading and examples to better understand this.  

But some further detail and clarification would be very useful !!!   But I think I get the gist of it....

Many thanks.

Patrick Grant

---

PG

Answer 3

Hi,
check my blog for setting the of dynamic file connection and Expressions.

---

Hope this helps !!
Sudeep   |    My Blog

Answer 4

No problem.

You'll be using the Foreach Loop Container to iterate over your files, I'm sure.  Using that will result in each file's name getting placed in an SSIS variable.

You'll put a Data Flow Task inside the Loop to read the file (using a Flat File Source, I imagine) and do your lookups and transformations.  You'll use a Derived Column transform inside that Data Flow to put the information encoded in the file name into a column of the data flow.  The BI Dev Network has some good info on expressions.

After the Data Flow Task, you'll put your File System Task(s).  The exercise you got building expressions for your Derived Column to decode your original file name will help you to build an expression to place on your destination file name variable.  Attach your File System Tasks to the Data Flow with precedence constraints (arrows toward the File System Tasks) to ensure the Data Flow opens and reads the file before you move it.

---

Answer 5

Thanks for the example. It is just what I need to help work this one out.  I have been thumbing through the SSIS Step by Step books and SQL server help and ther eis n ot much on File Tasks - and even less on changing file names dynamically and moving them.  Thanks Patrick

---

PG

Answer 6

Thanks for the pointer on the code.  I think what I am short of is examples of File Moving and changing names dynamically.  The SSIS Step by Step Book gives a page on File System Tasks and does not say much about them, and even less on scripting with them to change file names.  I will look throiugh the BI Network.  What I need is examples....

---

PG

Answer 7

The File System Task editor has properties like "IsSourcePathVariable" which doesn't mean "the source path changes dynamically" - it means "the source path comes from a variable". That's what you want, then you can pull down a list of variables to specify the "source" file name.

To set up an expression on a variable, do this:
1. I'm assuming you have your "original" file name in a variable - let's say it's called "SourceFileName".
2. Create a new variable, string type
3. Press F4 (to open the Properties window)
4. Find the EvaluateAsExpression property, change it to True
5. Find the Expression property, and click the ellipsis button (...) to open the Expression editor.
6. Enter an expression like this:

@[User::SourceFileName] + "_" + (DT_WSTR, 4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR, 2)DAY(GETDATE()), 2)

7. Press the "evaluate" button to see what you get.
8. Use the new variable as the "destination" variable in a File System Task.

---

Answer 8

Todd
Apologies for not responding more immediately, got caught up with Xmas and other stuff.  Your reply covers a lot of what I needed to know.  Thanks for that....

There is one more thing I need to know which is pretty important.  How can I obtain the file name in a VB Script?  (Assuming I am using a container to loop through multiple files.  )

I am planning to pick up the file name in the VB Script, generate a new file name, then process the file using its contents and the file name, then depending on the result of the processing, move it one one folder for successfull processing and another for failures....

Many thanks

Patrick

---

PG

Answer 9

If you are using a foreach loop container , with the file enumerator, you would be able to set up a variable to capture the file name while looping over the file list.  You could then read the variable inside of the script task either through the native variables box on the GUI or through the variable dispenser within the code (I would recommend the Reusable Functions for Reading and Writing Variables in SSIS Script Tasks by Daniel Read). 

---

Please mark answered posts. Thanks for your time.