I keep getting the error
The number of failing rows exceeds the maximum specified
Insert error, column 16('DateX', DB_TYPE_DBTIMESTAMP),
status 6: Data overflow. Invalid character value for cast
specification.

I have the following code in my VB script to capture
invalid dates. It is working fine in the test but it's not working when I
load the real data at the DTS. Below is my script that I have modified from
the link of http://www.sqldts.com/default.aspx?6,103,249,0,1


'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()


dim i_Day
dim i_Month
dim i_Year


i_Day = CInt(Left( DTSSource("Need By Date"), 2 ))
i_Month = CInt(Mid( DTSSource("Need By Date") ,4, 2 ))
i_Year = CInt(Right(DTSSource("Need By Date"),4))


DTSDestination("NEED_BY_DATE") = DateSerial( i_Year , i_Month ,i_Day )




Main = DTSTransformStat_OK
End Function

Thanks in advance help.

--
Message posted via http://www.sqlmonster.com

Sim,

What is 'DateX' declared as?

Ilya

"Sim Poay See via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Turn on logging for the DataPump. I would say that your real data has some
invalid values, but your test data does not.

You could pre-empt the failure by validating the values you get from the
string manipulation of the source column, and handle this row yourself.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Sim Poay See via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

Hi Darren,

The test data and the real data is the same. May be I can send you the
snapshot?

From
Sim

--
Message posted via http://www.sqlmonster.com

Hi IIya,

It should be 'NEED_BY_DATE' not 'DateX'.

From
Sim

--
Message posted via http://www.sqlmonster.com

I often have to get data from an AS/400 and on that platform they
frequently put a "low val" of 01/01/0001 in date fields instead of
NULLs. I would trap them with a VBScript using the "IsDate" function.
If it was not a valid date I handled it as the situation called for.

Hi All,

Thanks for fast feedback. I have found the solution for that. Just go to
control panel -> Regional setting -> Date -> Change the year from 2029 to
2060. Then open the SQL Enterprise Manager -> right click on your server
registration -> properties -> server setting tab -> two year digit ->
change to 2099 or what ever higher than the current setting.

From
Sim

--
Message posted via http://www.sqlmonster.com

i`m trying to use that simple code but it won`t works

my source data is varchar for exmple '20050101
and destination is smalldatetim

But i`m still getting an error with OVERFLOW
What`s wrong

Thanks for advice, Mate

The conversion here should be implicit but have a look here

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)

"matex81" <madamkie (AT) o2-dot-pl (DOT) no-spam.invalid> wrote