DTS - Extracting SQL data based on a Date   

SQL Server 2000 -- DTS Package Fails with error:  Invalid procedure
call or argument 'DATEDIFF'.  This works in a view.   Is there a
better way to do this?

What I am trying to do is transfer only yesterdays data (i.e., Now()
-1) to a delimited text file.


Function Main()

IF (DATEADD(dd, 0, DATEDIFF(dd, 0, ServerTimeStamp)) = DATEADD(dd, 0,
DATEDIFF(dd, 0, NOW())) - 1) THEN
	DTSDestination("ID") = DTSSource("ID")
	DTSDestination("Direction") = DTSSource("Direction")
	DTSDestination("ServerTimeStamp") = DTSSource("ServerTimeStamp")
	DTSDestination("Quality") = DTSSource("Quality")
	DTSDestination("Agency") = DTSSource("Agency")
	DTSDestination("LoggedOn") = DTSSource("LoggedOn")
	Main = DTSTransformStat_OK
		ELSE
	Main = DTSTransformStat_SkipRow
END IF
End Function

Thank you for any help with this.

RBollinger

date: Tue, 1 Jul 2008 12:58:59 -0700 (PDT)   author:   robboll

Re: DTS - Extracting SQL data based on a Date   

On Jul 1, 9:58 pm, robboll  wrote:
> SQL Server 2000 -- DTS Package Fails with error:  Invalid procedure
> call or argument 'DATEDIFF'.  This works in a view.   Is there a
> better way to do this?
>
> What I am trying to do is transfer only yesterdays data (i.e., Now()
> -1) to a delimited text file.
>
> Function Main()
>
> IF (DATEADD(dd, 0, DATEDIFF(dd, 0, ServerTimeStamp)) = DATEADD(dd, 0,
> DATEDIFF(dd, 0, NOW())) - 1) THEN
>         DTSDestination("ID") = DTSSource("ID")
>         DTSDestination("Direction") = DTSSource("Direction")
>         DTSDestination("ServerTimeStamp") = DTSSource("ServerTimeStamp")
>         DTSDestination("Quality") = DTSSource("Quality")
>         DTSDestination("Agency") = DTSSource("Agency")
>         DTSDestination("LoggedOn") = DTSSource("LoggedOn")
>         Main = DTSTransformStat_OK
>                 ELSE
>         Main = DTSTransformStat_SkipRow
> END IF
> End Function
>
> Thank you for any help with this.
>
> RBollinger

The error message is quite clear: Wrong datediff syntax.
Open a web browser, type www.google.it in the address bar, click
enter, type "dateadd visualbasic" and click i feel lucky...

PS: Why don't you use an easier execute sql task with sth like:
INSERT INTO TargetTable
SELECT * FROM SourceTable
WHERE yourDate = DATEADD(d, -1, GETDATE())

date: Wed, 2 Jul 2008 00:43:51 -0700 (PDT)   author:   matteus