My DTS package consists of two tasks, one... to transfer the data and two... to delete the data at the data source if the first task was successful, how can i cancel the first task if the deletion task is unsuccessful, i'm sending the same data twice because the first is allowed to retrieve the data but the second task can't delete the data because the table is locked.
Any better solutions or processes always welcomed
Much obliged

You do not cancel the task you use workflow constraints.

Introduction to Workflow
(http://www.sqldts.com/default.aspx?287)

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Gary Spence" <GarySpence (AT) discussions (DOT) microsoft.com> wrote

successful, how can i cancel the first task if the deletion task is
unsuccessful, i'm sending the same data twice because the first is allowed
to retrieve the data but the second task can't delete the data because the
table is locked.

In message <68F7F449-6378-43BD-A781-81BDDDCB3258 (AT) microsoft (DOT) com>, Gary
Spence <GarySpence (AT) discussions (DOT) microsoft.com> writes
What is locking the table or blocking the delete? Find the cause and fix
it or test for it first and abort the package before copying the data?

Or

Add some form of logic that keeps track of what has been transferred and
what hasn't to determine the source filter for the transfer. How easy
this is depends on your PK type and generation mechanism.

Or

If you need to ensure that the two tasks run as one transaction then you
can do this in DTS by setting up a package transaction. See Books Online
for more details about using transactions.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org