DTS create filename on the fly

[camy123]

hi im running a DTs package where i would like to create a excel filename on the fly(using Date) cuurently i just have a default filename and then rename it but it doesnt work ne one know why please

Function Main()


Dim FileDate
Dim DateString
Dim FSO
Dim File


FileDate = Date
DateString = DatePart("yyyy",FileDate) & Right("0" & _
DatePart("m",FileDate), 2) & Right("0" & DatePart("d",FileDate), 2)

Set FSO = CreateObject("Scripting.FileSystemObject")
Set File = FSO.GetFile("G:\England Marketing\Achiever_Reporting\counts_report.xls")

DTSDestination("colname") = DTSSource("colname")
DTSDestination("Total") = DTSSource("Total")


File.Name = "counts_report_" & DateString & ".xls"


Main = DTSTaskExecResult_Success

 

[JohnDTampaBay]

Is this an ActiveX task or transformation? Seems to be a task, so I don't get the lines:
DTSDestination("colname") = DTSSource("colname")
DTSDestination("Total") = DTSSource("Total")

Anyway, try this out:

Function Main()

Dim FileDate
Dim DateString
Dim FSO 
Dim File

        
FileDate = Date 
DateString = DatePart("yyyy",FileDate) & Right("0" & _
    DatePart("m",FileDate), 2) & Right("0" & DatePart("d",FileDate), 2)

Set FSO =  CreateObject("Scripting.FileSystemObject")
[s]Set File = FSO.GetFile("G:\England Marketing\Achiever_Reporting\counts_report.xls")[/s]

File = "G:\England Marketing\Achiever_Reporting\counts_report_" & DateString & ".xls"

fso.MoveFile "G:\England Marketing\Achiever_Reporting\counts_report.xls", File

[s]DTSDestination("colname") = DTSSource("colname")
DTSDestination("Total") = DTSSource("Total")[/s]

[s]File.Name = "counts_report_" & DateString & ".xls"[/s]

Main = DTSTaskExecResult_Success

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)