I am using a simple lookup liske this :

SELECT COUNT(USERID) AS countID
FROM dbo.user
WHERE (IS_ACTIVE = 'Y') AND (USERID= ?)

USERID =(numeric(3,0),not null)

and I am trying to send it the value through my activex code as this

IF DTSSource("Col002").Value <> "P1" AND IsNumeric(
DTSSource("Col002").Value ) THEN
IF DTSLookups("UserLookup").Execute(DTSSource("Col002 ").Value) > 0 THEN
Validation =TRUE
END IF
end if
but I'm getting this error:

microsoft ole db provider for sql server invalid character value for cast
specification


Thanks for your help

Have a look at all the values for Col002

Do this in QA

select ISNUMERIC('£')

It returns a 1 whereas this is most definitely not a number.

Allan

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Allan,

The problem was that in Destination Table,it was defined to be numeric with
precision 3 and I had a number with 10 digits in my source CSV file and I
had to add another condition in my validation stating that if the source is
bigger than 3 ,don't send it for lookup:-)


Thanks Allan for your nice replies.




"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote