Mark Forums Read

Using DTS to Extract Yesterday's Data

Go Back

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Reply

Page 1 of 2

1

#1

Old

robboll

Posts: n/a

Using DTS to Extract Yesterday's Data - 06-28-2008 , 08:48 AM

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

Reply With Quote

#2

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#3

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#4

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#5

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#6

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#7

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#8

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#9

Old

robboll

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-29-2008 , 04:12 PM

On Jun 28, 8:48*am, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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?

Reply With Quote

#10

Old

matteus

Posts: n/a

Re: Using DTS to Extract Yesterday's Data - 06-30-2008 , 10:41 AM

On Jun 28, 3:48 pm, robboll <robb... (AT) hotmail (DOT) com> wrote:

Quote:

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.

Reply With Quote

Reply

Page 1 of 2

1

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.