Using DTS to Extract Yesterday's Data   

I am trying to set up a DTS package that extracts all of Yesterdays
data.  The code that I am using works great in a view by converting a
DateTime stamp to just a short date, then displaying just the
GetDate() -1 (i.e., yesterday).  It does not work with DTS.  Any help
with this greatly appreciated.  Here is the DTS Trans Script:


Function Main()

IF (DATEADD(dd, 0, DATEDIFF(dd, 0, ServerTimeStamp)) = DATEADD(dd, 0,
DATEDIFF(dd, 0, GETDATE())) - 1) THEN
	DTSDestination("Type") = DTSSource("Type")
	DTSDestination("UnitID") = DTSSource("UnitID")
	DTSDestination("Quality") = DTSSource("Quality")
	DTSDestination("Agency") = DTSSource("Agency")
                DTSDestination("ServerTimeStamp") =
DTSSource("ServerTimeStamp")
	Main = DTSTransformStat_OK
		ELSE
	Main = DTSTransformStat_SkipRow
END IF
End Function

Thank you

RBolling

date: Sat, 28 Jun 2008 06:48:17 -0700 (PDT)   author:   robboll

Re: Using DTS to Extract Yesterday's Data   

On Jun 28, 8:48 am, robboll  wrote:
> I am trying to set up a DTS package that extracts all of Yesterdays
> data.  The code that I am using works great in a view by converting a
> DateTime stamp to just a short date, then displaying just the
> GetDate() -1 (i.e., yesterday).  It does not work with DTS.  Any help
> with this greatly appreciated.  Here is the DTS Trans Script:
>
> Function Main()
>
> IF (DATEADD(dd, 0, DATEDIFF(dd, 0, ServerTimeStamp)) = DATEADD(dd, 0,
> DATEDIFF(dd, 0, GETDATE())) - 1) THEN
>         DTSDestination("Type") = DTSSource("Type")
>         DTSDestination("UnitID") = DTSSource("UnitID")
>         DTSDestination("Quality") = DTSSource("Quality")
>         DTSDestination("Agency") = DTSSource("Agency")
>                 DTSDestination("ServerTimeStamp") =
> DTSSource("ServerTimeStamp")
>         Main = DTSTransformStat_OK
>                 ELSE
>         Main = DTSTransformStat_SkipRow
> END IF
> End Function
>
> Thank you
>
> RBolling

What is a method of moving data from a database with a datetime field
using DTS?  In VB I can use the DATEADD and DATEDIFF functions (shown
in the original post).  It doesn't work in a DTS package.  Does anyone
know how to do this using a DTS package?

date: Sun, 29 Jun 2008 14:12:38 -0700 (PDT)   author:   robboll

Re: Using DTS to Extract Yesterday's Data   

On Jun 28, 3:48 pm, robboll  wrote:
> I am trying to set up a DTS package that extracts all of Yesterdays
> data.  The code that I am using works great in a view by converting a
> DateTime stamp to just a short date, then displaying just the
> GetDate() -1 (i.e., yesterday).  It does not work with DTS.  Any help
> with this greatly appreciated.  Here is the DTS Trans Script:
>
> Function Main()
>
> IF (DATEADD(dd, 0, DATEDIFF(dd, 0, ServerTimeStamp)) = DATEADD(dd, 0,
> DATEDIFF(dd, 0, GETDATE())) - 1) THEN
>         DTSDestination("Type") = DTSSource("Type")
>         DTSDestination("UnitID") = DTSSource("UnitID")
>         DTSDestination("Quality") = DTSSource("Quality")
>         DTSDestination("Agency") = DTSSource("Agency")
>                 DTSDestination("ServerTimeStamp") =
> DTSSource("ServerTimeStamp")
>         Main = DTSTransformStat_OK
>                 ELSE
>         Main = DTSTransformStat_SkipRow
> END IF
> End Function
>
> Thank you
>
> RBolling

Hi,
GETDATE() is a TSQL-function, the related func in VB is NOW().
Try to replace in your code.

M.

date: Mon, 30 Jun 2008 08:41:23 -0700 (PDT)   author:   matteus