1

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.