Need help on DTSTransformation/DTSLookups

microsoft.public.sqlserver.tools

31 Mar 2006 7:12 PM

desperator

I have a Transformation task which invokes a DTSLookups (from the ActiveX
script). The lookup contains two sql statements separated by the semicolon,
like this:

update table1 set val = ? where id = ?; select @@ERROR, @@ROWCOUNT

This is how I invoke the lookup from the vbscript:

errorCodeArray = DTSLookups("mylookup").Execute(val1, val2)

I also have a step to handle the "On Failure" workflow.

Let's say for some reason the update statement fails, my "On Failure" task
will be executed. However, I can't seem to get the correct value for the sql
error code from the @@ERROR. It gives me a 0.

Can someone help me to capture the @@ERROR from the failed sql statement in
DTSLookups?

any help is appreciated.

D.