Forcing ExecutionResult (the value, not the flow)

Question

Forcing ExecutionResult (the value, not the flow)

Larry Stein

Default port

Points: 1447
More actions
December 4, 2003 at 3:10 pm

#106950

Oh - soooooooo close.
TheBeast asked a question about forcing ExecutionResult. The detailed situation appears to have had more to do with execution flow. I want control of the actual ExecutionResult.
I have a package that calls an ExecuteSQLTask. The stored procedure it executes encounters the infamous "Duplicate key was ignored". For those not lucky enough to have encountered it, this occurs when a unique index is defined WITH IGNORE_DUP_KEY and you attempt to insert a duplicate value. The statement succeeds despite an error message being returned, execution within the stored procedure continues, and all seems well.
The ExecuteSQLTask, however, sees this as failure. Fine - I can figure out if the failure was real or "fake" and branch accordingly using scripting tricks enumerated elsewhere on this and other sites (thanks, folks).
BUT - the overall package sees a step as having failed and my job executing this package shows failure.
How can I set (override) the ExecutionResult of the step that has completed? I want to do something like
set oStep = DTSGlobalVariables.Parent.Steps("MyFailingStep")
oStep.ExecutionResult.Value = DTSStepExecResult_Success
Am I missing something simple?
Thanks in advance.
Larry
December 8, 2003 at 12:00 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Unfortunatly The ExcecutionResult Step Property is Read only.

To bypass that behaviour you could handle errors through VB or use an activeX Script task to run your sp and handle errors on the script.

... VERY annoying

* Noel

data_czar Ten Centuries Points: 1147 More actions · Answer 2

Why would you havbe a "unique" index that you allow duplicates?

Could you call your stored proc from another proc (handling the 'error' in the new proc), then execute the new proc from DTS?

Larry Stein Default port Points: 1447 More actions · Answer 3

"why a unique index"

IGNORE_DUP_KEY does not allow duplicates to be inserted. It successfully inserts unique rows while ignoring (not inserting) the non-unique rows. It is one alternative to INSERT WHERE NOT EXISTS. I would not have chosen this method had I done the design. I will also say the designers had some valid reasons for considering this direction.

"wrapper proc"

Unfortunately, DTS still sees the error from the "inner" proc as an error, even though the message from IGNORE_DUP_KEY is not really an error - it was doing exactly what it was designed to do. This is a known problem with IGNORE_DUP_KEY and is discussed in some knowledge base articles.

Thanks,

Larry

xyz SSCommitted Points: 1653 More actions · Answer 4

The BOL states "SQL Server issues a warning and ignores the duplicate row."

Unfortunately, the sql server issues an error with level 16.

data_czar Ten Centuries Points: 1147 More actions · Answer 5

Could you use a Data Driven Query Task instead of the ExecuteSQL Task? Then you could trap the insert failures and 'skiprow'