Invalid data values

Question

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: 419 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

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 2

Colin Betteley

Hall of Fame

Points: 3095

May 26, 2006 at 8:19 am

#640332

Bob,

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 3

Open your Data Transformation task and go to the Options tab. Enter a path in the Exception File text box, then make the Max Error Count > 0.

Any errors that occur during the tranformation are written to this file. It will tell you the number of the row that failed and the data in that row.

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 4

Many thanks for the replies, this has got me hot on the scent of the problem.

Cheers

Colin