I'm new to DTS. I have the following problem: Im trying to update a record with an Data Driven query, but each time i execute it, the following error ocurrs.

"Syntax error converting the varchar value 'Main producer' to a column of data type smallint.

the query is:

UPDATE dbo.DistributionChannel
SET Descript = ?
WHERE (DistributionChannel = ?)

and the activex scipt is
Function Main()
DTSDestination("DistributionChannel") = (DTSSource("nIntertyp"))
DTSDestination("Descript") = DTSSource("sDescript")
IF IsEmpty (DTSLookups("BuscarDistChan").Execute(DTSSource("n Intertyp"))) THEN
Main = DTSTransformStat_InsertQuery
ELSE
Main = DTSTransformStat_UpdateQuery
END IF
End Function

Distributionchannel and nIntertyp are Smallint,
Descript and sDescript are vharchar(30)

Can anyone please tell me why this error is ocurring or what im doing wrong ???

Tks.
Rafa

In article <5360CDF0-ED9C-44AC-AB03-15A24300B300 (AT) microsoft (DOT) com>, Rafael
<anonymous (AT) discussions (DOT) microsoft.com> writes
and the activex scipt is
Function Main()
DTSDestination("DistributionChannel") =
(DTSSource("nIntertyp"))
DTSDestination("Descript") = DTSSource("sDescript")
IF IsEmpty (DTSLookups("BuscarDistChan").Execute(DTSSource(
"nIntertyp"))) THEN
Main = DTSTransformStat_InsertQuery
ELSE
Main = DTSTransformStat_UpdateQuery
END IF
End Function

Distributionchannel and nIntertyp are Smallint,
Descript and sDescript are vharchar(30)

Can anyone please tell me why this error is ocurring or what im doing
wrong ???

Tks.
Rafa

The error makes me think that you have got the two values the wrong way
round as I assume 'Main producer' is a description, and obviously it is
trying to convert it to the same type as the small int.

Double check the parameter mapping for both the insert and update
queries. Are you sure it is not the insert query that is raising the
error?

Are the input columns labelled badly, or do you perhaps have a badly
formatted files such that there really is text data in what should be a
int column, or even the file is corrupt such that it misreads the
values?
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org