DTS Type Mismatch at Join

Question

I am new to ETL and VB in general and need some help resolving this issue. I have looked online and found some interesting answers but none seem to be working for me.

varamt = Lookup (
    "Lookup_tbl1", 
    [Lookup_tbl1].[Amount], 
    Array(DTSSource("DEPT_ID"), DTSSource("ACCT_ID")))

IF NOT isEmpty(varamt) or NOT isNULL(varamt) or NOT varamt="" THEN
    DTSDestination("AMOUNT") = varamt
ELSE
    DTSDestination("AMOUNT") = DTSSource("AMOUNT")
END IF

Lookup_tbl1 has ACCT_ID and DEPT_ID as the primary keys. My destination table does not have these as primary key and ACCT_ID has NULL values in the table. Dept_ID and Acct_ID are varchar.

When I tried this transformation where ACCT_ID was notNULL, it worked, but not in the table where I want it to work.

Here is the error message that I get:

Inserting into [AMOUNT] ...
Error details: The number of failing rows exceeds the maximum specified.
(Microsoft Data Transformation Services (DTS) Data Pump (80020101): 
Error Code: 0 Error Source= Microsoft VBScript runtime error 
Error Description: Type mismatch: 'Join' Error on Line 36)
Execution Failed.`

There is no line 36 in my code. Please suggest solutions / pointers.

score 1 · Accepted Answer · edited Aug 26 '15 at 14:33

Ok. I was able to find the root cause (hopefully) and circumvent it. Most likely the reason was type mismatch 'join' was because ACCT_ID was NULL in the source table.

The below worked fine:

IF NOT isNULL(DTSSource("ACCT_ID"))
    varamt = Lookup ("Lookup_tbl1",
        [Lookup_tbl1].[Amount],
        Array(DTSSource("DEPT_ID"), DTSSource("ACCT_ID")))
    IF (NOT isEmpty(varamt) or NOT isNULL(varamt)) or varamt<>"" THEN
        DTSDestination("AMOUNT") = varamt
    ELSE
        DTSDestination("AMOUNT") = DTSSource("AMOUNT")
    END IF
ELSE
    DTSDestination("AMOUNT") = DTSSource("AMOUNT")
END IF

DTS Type Mismatch at Join

1 Answers1