I have the DTS package as follows:

Function Main()

dim i_Day
dim i_Month
dim i_Year

i_Day = Mid( DTSSource("Col002") ,7 , 2 )
i_Month = Mid( DTSSource("Col002") ,5 , 2 )
i_Year = Left(DTSSource("Col002"),4)

DTSDestination("Col001") = DTSSource("Col001")
if IsDate(i_Year & "-" & i_Month & "-" & i_Day) = true then
DTSDestination("Col002") =( i_Year & "-" & i_Month & "-" & i_Day )
else
msgbox "i_Year & "-" & i_Month & "-" & i_Day"
Main = DTSTransforStat_SkipRow
end if
DTSDestination("Col003") = DTSSource("Col003")
Main = DTSTransformStat_OK
End Function

~~~~~~~~~~~~~~~~~~~~~~~~~~

Here are a couple of bad date examples that I am running into: 19910631
and 19991299
Note the msgbox under the "else". This shows the two bad dates when
they happen, and I thought the Main = DTSTransforStat_SkipRow was
supposed to skip stamping the bad date to the smalldatetime field. The
DTS job fails with the error:

The number of failing rows exceeds the maximum specified.
Insert error, column 2 ('Col002', DBTYPE_DBTIMESTAMP), status 6: Data
overflow.
Invalid character value for cast specification.

Any suggestions greatly appreciated!!!

RBollinger

Update: I removed the 19910631 and 19991299 rows from the test data
source and it is still failing, so they apparently don't have anything
to do with it. So it looks like the
Main = DTSTransforStat_SkipRow is working -- but something else is
causing it to fail.


robboll wrote:

According to the error, the data pump is failing on trying to insert into the
destination table. Go to Properties of the Transform Data task, click on the
Options tab. Specify an exception file e.g. MyImport.txt, turn off 7.0
format, but turn on Source rows and Destination rows, and increase the
Maximum Error Count to 9999. Run the import and look for a file named
MyImport.txt.dest (if you used MyImport.txt as the file name). You will then
be able to look at the rows that fail. That might tell you why the rows are
failing.

Note that because your increased Maximum Error Count to a high number, most
of your rows will be imported. And remove the SkipRow stuff for this
troubleshooting.

Hope this helps.

Charles Kangai, MCT, MCDBA

Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services"
Author of Learning Tree's 4-day course: "SQL Server Reporting Services" URL:



"robboll" wrote:

you 'd MAN
Charles Kangai wrote:

Doing this the MyImport.txt file indicated two dates that are correct
and I don't understand why it fails when it hits them:

20800218
20820218

How I am asking the VBScript to enter it is as follows:

if DTSSource("Col004") = "99999999" then
Main = DTSTransforStat_SkipRow
else
if IsDate(mid(DTSSource("Col004"),1,4) & "/" &
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)) then
DTSDestination("DATE-EMP-END") = mid(DTSSource("Col004"),1,4) & "/"
& _
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)
else
DTSDestination("DATE-EMP-END") = null
end if
end if


Is there a regional setting or something that is seeing these dates as
errors?

Thanks,

RBollinger

Charles Kangai wrote:

Actually the script is a little redundant -- all I need is the date
check:

If IsDate(mid(DTSSource("Col004"),1,4) & "/" &
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)) then
DTSDestination("DATE-EMP-END") = mid(DTSSource("Col004"),1,4) & "/"
& _
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)
else
DTSDestination("DATE-EMP-END") = null
end if

BUT it's still generating the two date errors:
20800218
20820218

Thanks for any suggestions

robboll wrote:

Actually the script is a little redundant -- all I need is the date
check:

If IsDate(mid(DTSSource("Col004"),1,4) & "/" &
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)) then
DTSDestination("DATE-EMP-END") = mid(DTSSource("Col004"),1,4) & "/"
& _
mid(DTSSource("Col004"),5,2) & "/" & mid(DTSSource("Col004"),7,2)
else
DTSDestination("DATE-EMP-END") = null
end if

BUT it's still generating the two date errors:
20800218
20820218

Thanks for any suggestions

robboll wrote:

Is it possible that you are using smalldatetime instead of datetime?
Smalldatetime goes up to 2079. Datetime goes to year 9999.

Charles Kangai, MCT, MCDBA



"robboll" wrote:

That was it too. I guess it doesn't matter from an application
prespective if I change it from smalldatetime to datetime. Anyway,
it's done and seems to work fine. Thanks for your help!

Charles Kangai wrote: