DTS package and error handling

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS package and error handling

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-05 : 08:28:21

Phil writes "Hi,

I have a DTS package and one of the steps in the package is precedence "on success" for another step to occur. In the first step I do an T-SQL update statement. There is a possibility, due to bad data, that I get an error '512' subquery returns more than one value, etc. Even if this happens just once in a thousand records it makes the entire first step of my DTS package fail. Is there a way using @@error that I can capture this error and make the package think it was successful?

Thanks,
Phil"

Crespo

85 Posts

Posted - 2002-11-05 : 09:20:00

You should not use the 'On Success' workflow as it only executes the next step if there are no errors in the previous transaction.

Try using the 'On completeion' workflow as this will execute all the steps regardless of the errors that might occur prior to the current stage of the package life cycle

I am a big fan of the 'On Success' workflow because I would much rather stop the package when an error occurs but maybe in your case you can risk using the 'On completeion' workflow.

Piece of advice though... don't make a habit out of it. Stick to the 'On Success' as much as you can!

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-05 : 09:20:12

You can put the update in a step on it's own and go on to the next step on completion.

(Are you sure you want to use dts to control this?)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-05 : 09:21:25

I would advise you to try to make sure the query is "safe" first. If you can't do that, you could try making a procedure. That way you can control the error handling, and make the DTS believe that the step succeeds.

/Andraax

Crespo

85 Posts

Posted - 2002-11-05 : 10:29:09

quote:

You can put the update in a step on it's own and go on to the next step on completion.

(Are you sure you want to use dts to control this?)

I don't think I understood the question very well! Ooops!

But what you say would do the job for him....

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

tacket
Starting Member

47 Posts

Posted - 2002-11-06 : 15:48:14

Hello,

Thanks for the posts. I want to clear up my question some. I also like 'on success' better than 'on completion'. 'On success' might take a bit more hand holding in the beginning, but you will better trap errors in the long run.

My question is more a SQL question than DTS related. If I know an Update SQL statement has a good possibility of failing part of the time, due to bad data, and I know what is going to cause it to fail, is there a way using @@error that I can just ignore the error?

Something like

update table
set 1 = @one
where id = (select id
from table1
where 2 = @two)

if @@error = 512
Begin
'log this and go to next record'
End

I'm finding that in my cursor, even when one update fails it STILL goes through the whole bucket of records I have. In other words if I have 999 out of a 1000 bad records. It doesn't stop on record 1 just because it's bad or an update failed. It goes through all 1000 of them and tries to process.

The issue is that if one little error occurs, DTS thinks the whole package didn't work and any step I have after 'on success' won't get executed.

Any ideas?

Thanks,
Phil

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-06 : 18:02:12

Well you did get an error and dts rightly detects this.
Don't think there's any way round it.
Better to check the data before the update.

can you change the subquery to

where id in (...
or
where id = (select top 1 id

This would stop the failure and not do the update.
where id in (select id
from table1
where 2 = @two)
and
1 = (select count(*)
from table1
where 2 = @two)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tim
Starting Member

392 Posts

Posted - 2002-11-06 : 21:07:08

I think you should consider the multiphase data pump.

It gives you control at the row level rather than the task level.

Then you can trap individual row errors and take separate action, such as log it somewhere to follow up later, then continue with next row (if you wish). Which sounds like what you are after.

It won't perform as fast but that's because you are going row based in place of set based.

MPDP is pretty powerful when you are looking for control.

If you need help I recommend the chapter on it in Wrox publication, "Professional SQL Server 2000 DTS)\"

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

tacket
Starting Member

47 Posts

Posted - 2002-11-07 : 14:57:40

Heh,

I never thought about select top 1 id. That's not a bad idea. I'll have to look into Multi phase data pump :).

Thanks again,
Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources