DTS - Invalid Data Value in Destination Column

[WoolgR01]

Hi,

I am trying to import an excel spreadsheet into a SQL database. Some of the fields are foreign keys in other tables therefore I am using the DTS lookup functionality. I am stuck on the following transformation error message:
Invalid data value for 'DefinedTermID' in destination column.

My Tranformtation query is:
Function Main()
DTSDestination("DefinedTermID") = DTSSource("DefinedTerm")
intDT = DTSLookups("lkup_DefinedTerm").Execute(DTSSource(" DefinedTerm"))
DTSDestination("DefinedTermID") = intDT
Main = DTSTransformStat_OK
End Function

My Lookup Query is:
SELECT TypeID
FROM Web_Type
WHERE (TypeName LIKE '%' + ? + '%')

The source DefinedTerm is a string which I use in the lookup query to find the TypeID which returns the ID e.g. 189

The TypeID returned from the lookup is what I then want to use in the DefinedTermID field in the destination table which is an int.

When I test the Transofrmation query in the script window it seems to work ok, however when I run the DTS package which completes the insert into the table it fails.

Any help would be much appreciated.
Thanks,
Robert

[skhanal]

Testing the transformation runs for only few hundred records, that's why it may not have seen the error.

Does the select return 1 row only for all lookups?. Do you have null values in excel column?

[WoolgR01]

Thank you for your reply.

I am currently only running 3 records to test this lookup so that I can construct different querys for other column data.

In answer to your question the look up query only returns one row for each record and there are no null values.

[skhanal]

What is the error you get?. You can get package execution log by going to file - properties and set a log file.

[WoolgR01]

The error message from the log file is:
Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80020005): ActiveX Scripting Transform 'DTSTransformation__3' encountered an invalid data value for 'DefinedTermID' destination column.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
Thanks

[rmiao]

Looks like you should check data file to ensure it contains valid data for DefinedTermID column.

[skhanal]

It's seeing something it's not supposed to. Can you put

MsgBox DTSSource("DefinedTerm") & " " & intDT

after the dtslookups line to see what value the lookup is using

[WoolgR01]

Thanks for your reply.
Just to clarify the data file contains valid data, it contains a string (a valid TypeName in the table Web_Type) that I pass to the lookup query to return the id (TypeID). The 3 rows that I use in my testing return id's 189, 189 & 190 for each row. I set up a Msgbox alert in the VB script to tell me what each row was returned therefore I beleive the query's are doing the correct thing, however I am wondering about the format type. The destination table for DefinedTermID is an int. How can I tell what the variable type assigned to intDT is or convert it to ensure that it is an int in VB script?
skhanal - I'll add the msgbox now...
Thanks

[WoolgR01]

The message box results when run in the ActiveX script window are:
Compensation Events 189
Compensation Events 189
Relief Events 190
Which are the correct entries in the source data and the table id's.
When run in the DTS package the error occurs before any message box's are diplayed.
Thanks

[skhanal]

You can do

intDT = CInt(DTSLookups("lkup_DefinedTerm").Execute(DTSSou rce("DefinedTerm")))

[WoolgR01]

Thanks skhanal for persevering!
However I have tried using CInt but I still get the same error message. This DTS package is driving me made, first I got stuck with Lookups not working and then found that this was a microsoft issue and it required SP4 now the transformation which I thought would be simple has me stumped.

[WoolgR01]

Thanks for everyones help on this.
The workaround that I have implemented is to create a new sql table to export and manipulate my excel data to, then copy the data from this table to my destination table. This is down to what I beleive is happening in the transformation script the DTS package seems to be taking the excel column type as the format and then fails to import into my destination table becuase they are not the same type trying to change the type in the code seems to have no effect.