Dear all,
Using DTS i'm transferring 3 tables (Just select * statement ) from
database A and loading the 3 tables to database B repectively .
I need the tasks to be set like this:

1) There should be only one source and one target in the dts designer
sheet .
2) Every time before i load the data in database B the table should be
truncated.
3) Accidentally if the package execution stops working in the middle
,all the data's in the table B should be rolled back
I can achive the truncate statement by Execute sql task but i want to
know that
how the Data transformation task and the precedence constains will be
connected ....

Your answers are greatly appreciable.

Karthik

Dear Russel,
Thanks for the response ..
Yes i wish to roll back the truncation statement or delete statement
RLoski wrote:

With the restrictions you have put on yourself this will be made harder.

One Source and One destination means that you will need to recode all
the transformations in the transform data task each time you pick up the
new table. Why would having 3 sources and 3 destinations be an issue?

The truncate part is no problem (we can use an ExecuteSQL task to do
that before the transform data task) unless of course you insist that
this be done in the transform data task.


If on any of the tasks failing you would simply have an Error constraint
that points to database B and truncates the 3 load tables regardless of
if you loaded 3,2 or 1 of them.


--


Regards



Allan Mitchell


Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com


"karthik" <karthikisn (AT) gmail (DOT) com> wrote

One way to do this is to use a transaction. But then you need to use delete
rather than truncate so that you can roll back the delete.

You create one source connection A and one target connection B. You create
an ExecuteSQL statement on connection B with the statement "begin
transaction."

Next you create three sets of tasks: the first task in each set is a delete
statement on a table. The second is the data transform to that table. In
the options tab for the transform, uncheck the "use fast load" check box.
Create two ExecuteSQL tasks on Connection B with the statement "rollback
transaction." Using workflow, connect the first ExecuteSQL statement to the
transform, on success. Connect that first ExecuteSQL statement to one of the
rollback tasks using workflow on failure. Also connect the transform to the
other rollback task using workflow on failure.

Connect the three sets of tasks to each other. The transform task connects
to the delete task of the following set on success.

To round it out: connect the "begin transaction" task to the first set's
delete task on success. Create a "commit transaction" ExecuteSQL task on
connection B. Connect the final sets transform task to that commit task on
success.


--
Russel Loski, MCSD.Net


"karthik" wrote:

Another option is to load three staging tables (same structures as main
tables, different names). Then if all of the tables come over without error,
run a single ExecuteSQL statement that copies the data from the staging
tables to the main tables.
--
Russel Loski, MCSD.Net


"karthik" wrote:

Russell

You can rollback a Truncate as well.

use TempDB
GO
CREATE TABLE t(c1 int)
GO
INSERT t(c1) VALUES(1)

BEGIN TRANSACTION
TRUNCATE TABLE t
ROLLBACK TRANSACTION

SELECT * FROM t


--


Regards



Allan Mitchell


Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com


"RLoski" <RLoski (AT) discussions (DOT) microsoft.com> wrote