Hi There,

I'm reasonably new to S2K and DTS....and ODBC for that matter...so pls don't flame me if the solution is obvious......

My problem:

I'm trying to setup a dts package to execute a query on a informix db (via a odbc system dsn ) and copy the results to the local sql db (s2kdesttbl).

The package will be shed. to execute at regular intervals throughout the day.

The problem is avoiding duplications. The sql statement reads something like:

SELECT * FROM table WHERE trans_date = { fn CURDATE() }

This works great...up until the second execution where the sql statement returns rows from the FIRST and SECOND execution of the DTS package.

Now I though the most elegant way of dealing with this issue would be to use the DTS lookup feature to look at the exisiting data in the s2k db and use a lookup in the data transformation.


WHERE trans_date = { fn CURDATE() }
AND trans_ID IN ('?')


SELECT trans_ID from s2kdesttbl


DTSDestination("trans_ID") = DTSLookups("CheckID").Execute(DTSSource("trans_ID" ))


When I parse the sql and active X all OK. When I execute the package the follow error is returned.

[INFORMIX][INFORMIX ODBC DRIVER]Wrong Number of Parameters

I've tried a few different ways of doing this and the error remains the same. I can't find anything helpful of the error message. I'm assuming that I'm overlooking something really obvious to those with more knowlege than me, because if this type of scenario is beyond the scope of DTS then what is the use of the feature?

Any help much appreciated,.