Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    10

    Unanswered: DTS - AxScriptXform Invalid Data Value

    Hello,

    I'm trying to migrate a table from Oracle over to SQL Server. About half way through the migration, I get an error saying Invalid Data Value for 'SERVICE_START_DATE' source.

    I'm trying to locate the value in Oracle that is causing the error but I'm having trouble finding it.

    This is the code I'm using for the transformation:

    if isdate(DTSSource("SERVICE_START_DATE")) and DTSSource("SERVICE_START_DATE") > "1/1/2000" and DTSSource("SERVICE_START_DATE") < "1/1/2020" then
    DTSDestination("SERVICE_START_DATE") = DTSSource("SERVICE_START_DATE")
    else
    DTSDestination("SERVICE_START_DATE") = Null
    end if

    I can add on error resume next to the code but I would like to find the culprit.

    Thanks.

  2. #2
    Join Date
    Dec 2006
    Posts
    30
    I've seen empty Oracle dates cause this kind of thing. You might want to make sure that the fields don't contain empty values (not necessarily null values).

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I would just set SERVICE_START_DATE to varchar and see if the error still occurs (and to verify what is going to the SQL table)

  4. #4
    Join Date
    Feb 2007
    Posts
    10
    Thanks for the responses. I was able to pin point the record that was giving me trouble by looking at the exception file for source error rows (Options Tab in Transform Data Task Properties). The SERVICE_START_DATE was = 0007-08-27 00:00:00. I modified my transformation code to the following:

    if isdate(DTSSource("SERVICE_START_DATE")) and DTSSource("SERVICE_START_DATE") > datevalue("1/1/2000") and DTSSource("SERVICE_START_DATE") < datevalue("1/1/2020") then
    DTSDestination("SERVICE_START_DATE") = DTSSource("SERVICE_START_DATE")
    else
    DTSDestination("SERVICE_START_DATE") = Null
    end if

    I would like to set this particular date value and any future invalid dates to Null during the transformation. Does anyone have an idea on doing that? I thought my code would have picked up on this error but it didn't.

    Thanks.

  5. #5
    Join Date
    Feb 2007
    Posts
    10
    I figured it out

    if isdate(DTSSource("SERVICE_START_DATE")) = 1 then
    if DTSSource("SERVICE_START_DATE") > datevalue("1/1/2000") and DTSSource("SERVICE_START_DATE") < datevalue("1/1/2020") then
    DTSDestination("SERVICE_START_DATE") = DTSSource("SERVICE_START_DATE")
    end if
    else
    DTSDestination("SERVICE_START_DATE") = Null
    end if

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •