Rename Dynamic Filename

[Zurich98]

we have a process that drop off file daily to a specific folder on the network. The filename change each day. For example, today file is c:\Test\Employee07132009.xls. Tomorrow file will be c:\Test\Employee07142009.xls. How can I change the file name to c:\Test\Employee.xls. I'm using the ActiveX Script Task in SSIS 2005 with the following code but it does not work



Function Main()

Dim fso_obj,NFN

Set fso_obj = CreateObject("Scripting.FileSystemObject")

set NFN = "Employee"

fso_obj.MoveFile "C:\Test\*.xls", "C:\Test\" & NFN & ".xls"

Set objFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

your help is greatly appreciated.

Thanks

 

[Zurich98]

Set objFSO = Nothing should be

Set fso_obj = Nothing

Thanks

 

[PHV]

What about this ?

...
Dim strToday '07132009
strToday = Right("0" & Month(Now),2) & Right("0" & Day(Now),2) & Year(Now)
fso_obj.MoveFile "C:\Test\" & NFN & strToday & ".xls", "C:\Test\" & NFN & ".xls"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Zurich98]

Thank you for your input. Sorry, may be the original post is not clear on what I need. What I need to accomplish is as follow:

Some process drops a file daily at c:\Test location. The process labels the file in the following naming convention:

Employee07132009.xls --today file

for tomorrow, the process will label the file as

Employee07142009.xls

what I need is to rename the file from whatever the process named it to Employee.xls. This way, my next task will import this file into a SQL table. I need to have a static filename in order to automate the task that imports the data from the Exel file created by this process into a SQL table. Thanks