Forcing ExecutionResult (the value, not the flow)

Question

Post reply

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

noeld

SSC Guru

Points: 96590

December 8, 2003 at 1:46 pm

#485177

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

data_czar

Ten Centuries

Points: 1147

December 10, 2003 at 9:56 pm

#485647

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