Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Question Unanswered: VBScript Runtime Error: Type Mismatch in DTS

    Has anyone encountered the following prolem and how to solve it:

    I am trying to do a comparison between a global variable( defined in DTS as Decimal data type) and a Oracle Source column value( defined as Number field). The Global Variable(DWMaxID) is defined in a separate Execute SQL Task to hold a Max ID from a SQL server table. After completion the next step which is Transform Data Task should be executed based on the DWMaxID value.

    Here are the code:

    Function Main()
    If DTSSource("RESULT_ID")>DTSGlobalVariables("DWMaxID ").Value Then
    DTSDestination("RESULT_ID") = DTSSource("RESULT_ID")
    DTSDestination("RESULT_DT") = DTSSource("RESULT_DT")
    Main = DTSTransformStat_OK

    End If
    End Function

    I was able to parse the above ActiveX script but at run time always TYPE MISMATCH error.

    I have tryied the CAST and CONVERT in the IF statement and none works. Desperately need an answer to it.


    Thanks.

    Debbie

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Could you try testing for a numeric value?
    Code:
    Function Main()
        If IsNumeric(DTSSource("RESULT_ID")) Then
    	If DTSSource("RESULT_ID")>DTSGlobalVariables("DWMaxID").Value Then
    		DTSDestination("RESULT_ID") = DTSSource("RESULT_ID")
    		DTSDestination("RESULT_DT") = DTSSource("RESULT_DT")
    		Main = DTSTransformStat_OK
    
    	End If
        Else
    	msgbox "Invalid result ID " & CSTR(DTSSource("RESULT_ID"))
        End If
    End Function
    MCDBA

  3. #3
    Join Date
    Jul 2003
    Posts
    2
    Thanks for the reply. It's still the same error msg for type mismatch. I am wondering if it's possible at all to do this type of comparison between Oracle and SQL server?

    Originally posted by achorozy
    Could you try testing for a numeric value?
    Code:
    Function Main()
        If IsNumeric(DTSSource("RESULT_ID")) Then
    	If DTSSource("RESULT_ID")>DTSGlobalVariables("DWMaxID").Value Then
    		DTSDestination("RESULT_ID") = DTSSource("RESULT_ID")
    		DTSDestination("RESULT_DT") = DTSSource("RESULT_DT")
    		Main = DTSTransformStat_OK
    
    	End If
        Else
    	msgbox "Invalid result ID " & CSTR(DTSSource("RESULT_ID"))
        End If
    End Function

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Sorry for the delay in response.

    So you get the same error message and not the prompt? Therefore I'm wondering if it is not RESULT_ID that is giving you the error but RESULT_DT? I know that somethings certain date formats will cause errors. Maybe on your source (ORACLE SELECT) you can format the date into something like DD-MMM-YYYY, like "10-JUL-2003", using words instead of the traditional numeric DD-MM-YYYY. Sometimes the system assumes months are where the days are.
    MCDBA

Posting Permissions

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