DTS and handling dates

I am trying to DTS some data from delimited source and some of the
dates have the value 99999999. This naturally makes the DTS job fail.
Is there a method to make it ignore these values and append only those
with acceptable date formats?: 20060810
Thanks for any suggestions.
RBollingerThere are probably quite a few different ways...it all
depends as to what best meets the overall needs for the
package.
One option would be to use a query against the text source
to select the rows you want to import using whatever
criteria against the date column.
Another would be to use an ActiveX Transformation script
with the Transform Data task, check the value of the column
with the date value and if it's not a date or in whatever
format, use DTSTransformStat_SkipInsert
to skip the row.
And another option would be to import the data into a
staging table, use a transform data task and query the
staging table for rows with the appropriate values for the
date column.
-Sue
On 10 Aug 2006 14:09:57 -0700, "robboll"
<robboll@.hotmail.com> wrote:

>I am trying to DTS some data from delimited source and some of the
>dates have the value 99999999. This naturally makes the DTS job fail.
>Is there a method to make it ignore these values and append only those
>with acceptable date formats?: 20060810
>Thanks for any suggestions.
>RBollinger|||Making a staging table isn't an option for me so I'll use the
Transformation script:
The source is a text file and I am having difficulty with the date.
The script is as follows (it may wrap):
'***************************************
*******************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'***************************************
*********************************
Function Main()
DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
if DTSSource("Col002") = "99999999" then
Main = DTSTransforStat_SkipRow
else
DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
DTSSource("Col002"))
end if
DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
Main = DTSTransformStat_OK
End Function
This is bombing with a type mismatch 'CONVERT' error. Any suggestions
appreicated.
RBollinger
Sue Hoegemeier wrote:[vbcol=seagreen]
> There are probably quite a few different ways...it all
> depends as to what best meets the overall needs for the
> package.
> One option would be to use a query against the text source
> to select the rows you want to import using whatever
> criteria against the date column.
> Another would be to use an ActiveX Transformation script
> with the Transform Data task, check the value of the column
> with the date value and if it's not a date or in whatever
> format, use DTSTransformStat_SkipInsert
> to skip the row.
> And another option would be to import the data into a
> staging table, use a transform data task and query the
> staging table for rows with the appropriate values for the
> date column.
> -Sue
> On 10 Aug 2006 14:09:57 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>|||You get the error because you are in an ActiveX script using
VBScript and you are trying to use T-SQL syntax in the
VBScript. So convert won't work. VBScript conversion
functions start with C followed by the abbreviated data
type. CInt for Integer, CDate for Date. Try CDate.
-Sue
On 12 Aug 2006 14:51:37 -0700, "robboll"
<robboll@.hotmail.com> wrote:
[vbcol=seagreen]
>Making a staging table isn't an option for me so I'll use the
>Transformation script:
>The source is a text file and I am having difficulty with the date.
>The script is as follows (it may wrap):
> '***************************************
*******************************
>' Visual Basic Transformation Script
>' Copy each source column to the
>' destination column
> '***************************************
*********************************
>Function Main()
> DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> if DTSSource("Col002") = "99999999" then
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
>DTSSource("Col002"))
> end if
> DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
>Main = DTSTransformStat_OK
>End Function
>This is bombing with a type mismatch 'CONVERT' error. Any suggestions
>appreicated.
>RBollinger
>
>
>
>Sue Hoegemeier wrote:|||Thanks!
Sue Hoegemeier wrote:[vbcol=seagreen]
> You get the error because you are in an ActiveX script using
> VBScript and you are trying to use T-SQL syntax in the
> VBScript. So convert won't work. VBScript conversion
> functions start with C followed by the abbreviated data
> type. CInt for Integer, CDate for Date. Try CDate.
> -Sue
> On 12 Aug 2006 14:51:37 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>