DTS - fault tolerance
-
Hello - I have a number of exec SQL tasks in a DTS package. If one fails (i.e. tries to drop a table that doesn't exist) then the whole package fails. I haven't set fail on first error on the package properties. Also even if I set on completion workflow it still seems to bomb if it encounters an error whilst perfoming any task (SQL,activex,exec package). Any help appreciated -thanks
-
I can help with the example you gave of dropping a non existant table. For each table you try to drop use this code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TableName]
GO
Then you won't get an error when a task tries to drop a table.
Did you set all the workflows to on completion? What kind of errors do you get?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I had problems with an activex script that wrote to a log file whilst another job still had a lock on it. It bombed out and the job failed even with 'on completion' workflow. However just spenty an hour trying to replicate with everything sent to on complete and it all does what it's supposed to!!! Also introduced the if table exists to all my drop table logic. Thanks for your help
-
make sure to use the "on error" on your activex scripts too
* Noel -
In my experience it is best to avoid the "on completion" constraint. It is much better and safer to setup your tasks to avoid failures (as illustrated by rmarda) and use the "on success" and "on failure" constraints to provide the workflow.
--------------------
Colt 45 - the original point and click interface
-
When I've used 'on completion' I've had some 'unexpected' results so I'll try and stick with on success where posssible.
Had a read around 'on error' and definitely looks like something I should be using - thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply