MS SQL Server DTS - DTS Issue

Author

DTS Issue - Urgent

Marco Pais

2005-07-20, 11:23 am

Hi there,

I have created a DTS that copies data from a table in SQL Server to a table
with the same structure, but in Oracle.

Lets suppose I have 100 rows in the origin table. If the DTS fails in he
middle of the operation, supposing the row 55 has an error, the previous
rows are copied to the Oracle table.

How can I now wich rows have been copied?

The DTS runs periodicaly, and I need to know wich rows are copied, so I can
block them on the SQL Server, so they can not be copied again to Oracle.

Can anybody help me? Thanks in advance.

Regards,

Marco

Rick Gittins

2005-07-20, 11:23 am

Do the tables have a primary key? If they do you can write a query to
exclude the records already transformed.

Rick

"Marco Pais gmail.com>" <marco.pais@<IGNORE> wrote in message
news:%23jYK9yTjFHA.2156@TK2MSFTNGP14.phx.gbl...
> Hi there,
>
> I have created a DTS that copies data from a table in SQL Server to a
> table
> with the same structure, but in Oracle.
>
> Lets suppose I have 100 rows in the origin table. If the DTS fails in he
> middle of the operation, supposing the row 55 has an error, the previous
> rows are copied to the Oracle table.
>
> How can I now wich rows have been copied?
>
> The DTS runs periodicaly, and I need to know wich rows are copied, so I
> can
> block them on the SQL Server, so they can not be copied again to Oracle.
>
> Can anybody help me? Thanks in advance.
>
> Regards,
>
> Marco
>

Marco Pais

2005-07-20, 11:23 am

Hi.

They have a primary key. How/where can I write that query?

Marco

"Rick Gittins" <rgittins@dungarvin.com> escreveu na mensagem
news:%23ajiG4TjFHA.1044@tk2msftngp13.phx.gbl...

> Do the tables have a primary key? If they do you can write a query to
> exclude the records already transformed.
>
> Rick
>
> "Marco Pais gmail.com>" <marco.pais@<IGNORE> wrote in message
> news:%23jYK9yTjFHA.2156@TK2MSFTNGP14.phx.gbl...
>
>

Narayana Vyas Kondreddi

2005-07-20, 1:23 pm

I would suggest you first get your data from SQL Server to a temporary table
in Oracle, and then use the MINUS operator to work out which rows don't
already exist in your main Oracle table.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/

"Marco Pais" <IGNOREmarco.pais@gmail.com> wrote in message
news:O81Kk9TjFHA.3348@tk2msftngp13.phx.gbl...
> Hi.
>
> They have a primary key. How/where can I write that query?
>
> Marco
>
> "Rick Gittins" <rgittins@dungarvin.com> escreveu na mensagem
> news:%23ajiG4TjFHA.1044@tk2msftngp13.phx.gbl...
>
>
>

Darren Green

2005-07-20, 8:24 pm

In message <uroCkbVjFHA.3316@TK2MSFTNGP14.phx.gbl>, Narayana Vyas
Kondreddi <answer_me@hotmail.com> writes
>I would suggest you first get your data from SQL Server to a temporary table
>in Oracle, and then use the MINUS operator to work out which rows don't
>already exist in your main Oracle table.

It might be a bit expensive if you have a lot of rows. Perhaps you could
use a date parameter to exclude old rows, and only transfer the latest
rows. If you can rely on dates or some other regularly increasing (key)
value. If it is not accurate enough transfer a bit more than you need
then use the MINUS idea as above, but it should save all data moving
across.

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

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

Sponsored Links