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