Hi All


I've the following task
Need to copy data from ServerA and from 5 tables t
another ServerB at regular intervals or on demand. W
need to transfer based on some condition
Both are SQL Servers

The data collected from ServerA has to go to
different tables in ServerB
There are some join keys on ServerA (table joins)

This task has been accomplished using a store
procedure
But I'm exploring the possibility of doing it thr
DTS


How can the following tasks can be accomplished usin
DTS

1) I need to handle errors at each insertion into the destination
tables. How can I do this

2) How can I handle Cursor operations using DTS

3) Before inserting into the destination tables,
need to verify for duplicates. If record alread
exists, I need to insert in another table and leave i
for manual verification. How can I handle this

4) Once the inserts are successfully done, I need t
update another table with 'status updated'

Can anybody explain how the above tasks can b
accomplished using DTS, in particular cursor op

Thanks much for any inputs, ideas etc.

Siva.

Wow a lot of questions

1. Error handing is not great in DTS 2000 but you do have workflow which
allows you to branch off if you error in a task. In 2000 you also have
(Last tab Data Pump) the opportunity to log those erroneous rows to a text
file for looking at later.

2. Find another way of doing it. Why do you need a cursor? Cursors are in
a lot of cases avoidable.

3. You could have ServerB as a linked server to ServerA. You can then
compare key values to check for duplicates.

4. An ExecuteSQL task will take care of this



--
--

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

"Siva Gort" <siva116 (AT) yahoo (DOT) com> wrote