sql server dts: DatePart in a DTS -- sql server dts

DatePart in a DTS

jmawebco
6/7/2007 6:40:29 AM

sql server dts:

Can someone tell me how to make "fixDate" in the script below be equal
to Now() minus one day so that it is equal to yesterday?

I am using the Funtion shown below to locate a file in a remote ftp
server which has a name ending with a date value in yyyymmdd format.
The problem I have is that when the dts runs the value for fixDate
never maches any of the values in the ftp server so it never finds the
file and fails.

If someone can help me out I would be very grateful.

Function Main()

Dim oPkg
' Get current package instance
Set oPkg = DTSGlobalVariables.Parent
' Get existing FTP task by name
' Note, name may need to be changed to match your FTP task
Set oFTPTask = oPkg.Tasks("DTSTask_DTSFTPTask_1").CustomTask
dim sFilename
dim fixDate
fixDate = Now() *********NEED TO SET THIS TO NOW MINUS ONE
DAY***************
sFilename = Right(Year(fixDate), 4)
If Month(fixDate) < 10 Then sFilename = sFilename & "0" &
Month(fixDate) Else sFilename = sFilename & Month(fixDate)
If Day(fixDate) < 10 Then sFilename = sFilename & "0" & Day(fixDate)
Else sFilename = sFilename & Day(fixDate)
sfilename = sfilename & ";;;"
' Set properties of FTP task and filename
oFTPTask.SourceFilename = "DAILYONROAD." & sFilename
' Clean Up
Set oFTPTask = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success

End Function

Re: DatePart in a DTS

Bob
6/7/2007 2:07:19 PM

dateadd should get you there.
? dateadd("d",-1,date())
6/6/2007

hth,
bob.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.