Invalid data values

Question

Post reply

Invalid data values

Colin Betteley

Hall of Fame

Points: 3095
More actions
May 26, 2006 at 7:57 am

#70413

Hello,

I have a DTS that extracts data from Oracle to SQL Server 2000. One of the attributes is a date and in order to avoid "funny data" I use the following ActiveX transformation Script.

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

' Copy each source column to the destination column

Function Main()

            dim srcDate

            srcDate = DTSSource("startDate")

             if isDate(srcDate) then

                        if srcDate > #1 jan 1900# and srcDate < #1 jan 2050# Then

                                    DTSDestination("startDate") = srcDate

                        else

                                    DTSDestination("startDate") = Null

                        end if

            else

                        DTSDestination("startDate") = Null

            end if

            Main = DTSTransformStat_OK

End Function

   If i run the Transformations on their own all appears to be OK. If I run the DTS as a whole a get an error after 30,000 rows "Invalid Data Values".

    The script should convert any non date data to a NULL. Is there any way to capture the line causing the problem? Why should it work when run in isolation but not as part of the overall package.

All comments welcomed. Thank you.

Colin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rduniway SSC-Addicted Points: 409 More actions · Answer 1

I can't tell you from your code why it shouldn't work, though I've had similar issues in the past using very similar code. The next step I would take would be to load in the source file into a table where every column is defined as varchar(255) with an integer ID column added. You can then go to the row identified in the error message, and look at what is in the startDate column to understand the source of the error. My best guess is that you have a date format that VB script recognizes but SQL Server 2000 doesn't, though what format that could be isn't obvious. Being able to actually see the value in that row will help you unpack the puzzle. Hope it helps.

Bob